Home |
Search |
Today's Posts |
#1
|
|||
|
|||
A formula that is: if the sum is this, then muliply by this?
I want a formula that will calculate like this:
If the total in this cell is between 75,000 and 99,999 then muliply it by ..30. If it's between 100,000 and 149,999 then muliply it by .80. Is this possible? |
#2
|
|||
|
|||
one way:
=IF(OR(A1<75000,A1=150000),"out of range",IF(A1<100000,0.3,0.8)*A1) In article , "Jenny" wrote: I want a formula that will calculate like this: If the total in this cell is between 75,000 and 99,999 then muliply it by .30. If it's between 100,000 and 149,999 then muliply it by .80. Is this possible? |
#3
|
|||
|
|||
This lookup formula will do it.
=LOOKUP(SUM(A7:A10),{0,75000,100000,150000},{0,0.3 ,0.8}) =LOOKUP(SUM(A7:A10),{0,75000,100000,150000},{0,0.3 ,0.8})*sum(a7:a10) Don Guillett SalesAid Software "Jenny" wrote in message ... I want a formula that will calculate like this: If the total in this cell is between 75,000 and 99,999 then muliply it by .30. If it's between 100,000 and 149,999 then muliply it by .80. Is this possible? |
#4
|
|||
|
|||
Note that this gives #N/A for values 150000. IF it's desired that those
values return 0, then you could use =LOOKUP(SUM(A7:A10),{0,75000,100000,150000,1E+307} ,{0,0.3,0.8,0}) * SUM(A7:A10) In article , "Don Guillett" wrote: =LOOKUP(SUM(A7:A10),{0,75000,100000,150000},{0,0.3 ,0.8})*sum(a7:a10) |
#5
|
|||
|
|||
Jenny Wrote: I want a formula that will calculate like this: If the total in this cell is between 75,000 and 99,999 then muliply it by ..30. If it's between 100,000 and 149,999 then muliply it by .80. Is this possible? Try this formula ... =if(and(A1=75000,A1<=99999),0.30*A1,if(and(A1=10 0000,A1<=149999),0.80*A1,"")) -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=346866 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |