Formula?

[newby1273]

Here is what I want to do:
If a cell (formatted as number) is between 0 and 10 multiply it by this. If
it's between 10 and 20 multiply it by that. How do you write that?

thx

 

[pdberger]

Try this:

A B
1 this that
2
3 15 =IF(A3<10,A3*A1,IF(A3<20,A3*B1,"Huh?"))

Or you can just put the multipliers into the formula itself, replacing A1 &
B1. The last "Huh?" just lets you know that the input variable was over 20.

HTH

 

[Dave]

Hi, Try this.
If your value is in A1, place this in A2
=IF(A1<=10,A1*this,IF(A1<=20,A1*that,"neither this nor that"))
Regards - Dave.

 

[Brad]

Couple of questions - what happens if the cell is less than 0 or more than 20

If the cell will always be between 0 and twenty

=if(c1<=10,c1*number1,c1*number2)

If the number is exactly 10 and you want it to be multiplied by number2

=if(c1<10,c1*number1,c1*number2)

The other post have additional "checks" for value - which is fine but they
are not needed.

 

[newby1273]

I tried this but not working:
=IF(F6<=13,F6*13.10,IF(F6<=32,F6*14.13),IF(F6<=52,F6*14.99),IF(F6<=70,F6*16.16),IF(F6<=90,F6*16.74),IF(F6<=100,F6*19.67)).
I think it's because lets say the number was 16 - that means it matches ALL
the rest of the IF's right? So that won't work. Any ideas?

 

[Brad]

"()" problem
=IF(F13<=13,F13*13.1,IF(F13<=32,F13*14.13,IF(F13<=52,F13*14.99,IF(F13<=70,F13*16.16,IF(F13<=90,F13*16.74,IF(F13<=100,F13*19.67)))))

Takes care of one problem - need to address when f13 (or f6) > 100

=IF(F13<=13,F13*13.1,IF(F13<=32,F13*14.13,IF(F13<=52,F13*14.99,IF(F13<=70,F13*16.16,IF(F13<=90,F13*16.74,IF(F13<=100,F13*19.67,F13*20)))))

You might consider using lookup tables - much cleaner than what you are
doing now...

 

[newby1273]

Thanks!

"()" problem
=IF(F13<=13,F13*13.1,IF(F13<=32,F13*14.13,IF(F13<=52,F13*14.99,IF(F13<=70,F13*16.16,IF(F13<=90,F13*16.74,IF(F13<=100,F13*19.67)))))

Takes care of one problem - need to address when f13 (or f6) > 100

=IF(F13<=13,F13*13.1,IF(F13<=32,F13*14.13,IF(F13<=52,F13*14.99,IF(F13<=70,F13*16.16,IF(F13<=90,F13*16.74,IF(F13<=100,F13*19.67,F13*20)))))

You might consider using lookup tables - much cleaner than what you are
doing now...

 

[Brad]

If you would be so kind as "click" the button that indicates your question
has been answered, that would be great.