if a value is greater than then multiply

Hi Ismex,

As written, you won't be able to get three different results, as you specified only a single value, Replacing the second and third x in your question with y, a pair of nested IFs will do the job:

x=10, y=50

=IF(A2<10,A2*1,IF(A2<50,A2*0.05,A2*0.07))

Regards,

Barry

your rules are ambiguous...:

As stated

if a value in a cell is less than x number then multiply by 1, if it is greater than but less than x number then multiply by .05, if it is equal or greater than x number then multiply by .07.

if a value in a cell is less than x number then multiply by 1 [else]

if it [value] is greater than but less than x number then multiply by .05 [else]

if it is equal or greater than x number then multiply by .07

"greater than but less than" is, well, not possible.

So I changed the rule and guessed at your intent. hopefully the following will help you or you can post back with an updated rule

The formula in cell E2 is:

=IF(B2<C2, 1, IF(B2<D2, 0.5, 0.7))

this follows the rule:

if the value (cell B2) is less than X (cell C2) then return 1 else

if the value (cell B2) is less than Y (cell D2) then return 0.05 else

return 0.07

you can use the result of this formula to multiply times whatever you want.

Hi Ismex,

Two comments on your formula:

IF(B15<20,B15×1,IF(B15<30,B15×0.05,IF(B15>30,B15×0.07)))

• Control is passed to the second IF only only if the first returns FALSE and to the third only if both the first and the second return FALSE. If the third test also returns FALSE (which it will if the value is exactly 30) this formula will return FALSE.
• If you want the formula to multiply be 0.7 for values of 30 or more, replace > with >= (which Numbers will replace with ≥).
• But a more efficient solution is to eliminate the unnecessary third IF.
  IF the value is NOT less than 15 AND it is NOT less than 30, then it is equal to or greater than 30, and the third IF will always return TRUE.
  

IF(B15<20,B15×1,IF(B15<30,B15×0.05,B15×0.07))

EDIT:

Wayne's editing can be applied here as well:

IF(B15<20,1,IF(B15<30,0.05,0.07))*B15

Regards,

Barry

The formula:

IF(B15<20,B15×1,IF(B15<30,B15×0.05,SI(B15>30,B15×0.07)))

can be written most concisely:

IF(B15<20,1,IF(B15<30,0.05,SI(B15>30,0.07)))*B15

combining my previous comment with Barry's latest:

IF(B15<20,B15×1,IF(B15<30,B15×0.05,B15×0.07))

can be written as:

IF(B15<20,1,IF(B15<30,0.05,0.07))*B15