| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula?
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formula?
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 "newby1273" wrote: 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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formula?
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. "newby1273" wrote: 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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formula?
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. -- Wag more, bark less "newby1273" wrote: 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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formula?
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? "newby1273" wrote: 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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formula?
"()" 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*1 6.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*1 6.74,IF(F13<=100,F13*19.67,F13*20))))) You might consider using lookup tables - much cleaner than what you are doing now... -- Wag more, bark less "newby1273" wrote: 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? "newby1273" wrote: 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 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formula?
Thanks! :)
"Brad" wrote: "()" 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*1 6.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*1 6.74,IF(F13<=100,F13*19.67,F13*20))))) You might consider using lookup tables - much cleaner than what you are doing now... -- Wag more, bark less "newby1273" wrote: 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? "newby1273" wrote: 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 |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formula?
If you would be so kind as "click" the button that indicates your question
has been answered, that would be great. -- Wag more, bark less "newby1273" wrote: Thanks! :) "Brad" wrote: "()" 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*1 6.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*1 6.74,IF(F13<=100,F13*19.67,F13*20))))) You might consider using lookup tables - much cleaner than what you are doing now... -- Wag more, bark less "newby1273" wrote: 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? "newby1273" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
