| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if and or ifthen?
I have a formulat that is not working.
Basically I want a formula that will take up to $100,000 and multiply the cost per thousand by $5.75 if the amount is over 100,000 to $1,000,000 I want it to take the first 100,000 and times it's cost per thousand by $5.75 and everything over that by $5.00. This is what I have came up with, but it is not working. Then if it is $1,000,000.00 or more I would like it to say "call for quote" any suggestions would be appreciated. Thank you =IF(B30<1,"0",IF(B30<=100000,SUM(B30/1000)*5.75,IF(B30=100001,AND(B30<1000000),SUM(B30-100000)/1000*5+575))) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if and or ifthen?
=MIN(100000,A1)*5.75+MAX(0,A1-100000)*5
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Pam" wrote in message ... I have a formulat that is not working. Basically I want a formula that will take up to $100,000 and multiply the cost per thousand by $5.75 if the amount is over 100,000 to $1,000,000 I want it to take the first 100,000 and times it's cost per thousand by $5.75 and everything over that by $5.00. This is what I have came up with, but it is not working. Then if it is $1,000,000.00 or more I would like it to say "call for quote" any suggestions would be appreciated. Thank you =IF(B30<1,"0",IF(B30<=100000,SUM(B30/1000)*5.75,IF(B30=100001,AND(B30<10000 00),SUM(B30-100000)/1000*5+575))) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if and or ifthen?
Try:
=IF(B30<1,0,IF(B30<=100000,(B30/1000)*5.75,IF(AND(B30=100001,B30<1000000),(B30-100000)/1000*5+575,"Call for quote"))) HTH "Pam" wrote: I have a formulat that is not working. Basically I want a formula that will take up to $100,000 and multiply the cost per thousand by $5.75 if the amount is over 100,000 to $1,000,000 I want it to take the first 100,000 and times it's cost per thousand by $5.75 and everything over that by $5.00. This is what I have came up with, but it is not working. Then if it is $1,000,000.00 or more I would like it to say "call for quote" any suggestions would be appreciated. Thank you =IF(B30<1,"0",IF(B30<=100000,SUM(B30/1000)*5.75,IF(B30=100001,AND(B30<1000000),SUM(B30-100000)/1000*5+575))) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if and or ifthen?
..or...
=IF(B30<1,"0",IF(B30<=100000,(B30/1000)*5.75,IF(B30<1000000,(B30-100000)/1000*5+575,"Call for quote"))) "Pam" wrote: I have a formulat that is not working. Basically I want a formula that will take up to $100,000 and multiply the cost per thousand by $5.75 if the amount is over 100,000 to $1,000,000 I want it to take the first 100,000 and times it's cost per thousand by $5.75 and everything over that by $5.00. This is what I have came up with, but it is not working. Then if it is $1,000,000.00 or more I would like it to say "call for quote" any suggestions would be appreciated. Thank you =IF(B30<1,"0",IF(B30<=100000,SUM(B30/1000)*5.75,IF(B30=100001,AND(B30<1000000),SUM(B30-100000)/1000*5+575))) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if and or ifthen?
Thanks, the "toppers" one works. The min/max doesn't return the correct
value, but perhaps I have to change the min/max values? I have never used that function, but the formula looks much cleaner. "Toppers" wrote: Try: =IF(B30<1,0,IF(B30<=100000,(B30/1000)*5.75,IF(AND(B30=100001,B30<1000000),(B30-100000)/1000*5+575,"Call for quote"))) HTH "Pam" wrote: I have a formulat that is not working. Basically I want a formula that will take up to $100,000 and multiply the cost per thousand by $5.75 if the amount is over 100,000 to $1,000,000 I want it to take the first 100,000 and times it's cost per thousand by $5.75 and everything over that by $5.00. This is what I have came up with, but it is not working. Then if it is $1,000,000.00 or more I would like it to say "call for quote" any suggestions would be appreciated. Thank you =IF(B30<1,"0",IF(B30<=100000,SUM(B30/1000)*5.75,IF(B30=100001,AND(B30<1000000),SUM(B30-100000)/1000*5+575))) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if and or ifthen?
To allow for costs per 1000:
=MIN(100000,B30)*0.00575+MAX(0,(B30-100000))*0.005 "Toppers" wrote: Try: =IF(B30<1,0,IF(B30<=100000,(B30/1000)*5.75,IF(AND(B30=100001,B30<1000000),(B30-100000)/1000*5+575,"Call for quote"))) HTH "Pam" wrote: I have a formulat that is not working. Basically I want a formula that will take up to $100,000 and multiply the cost per thousand by $5.75 if the amount is over 100,000 to $1,000,000 I want it to take the first 100,000 and times it's cost per thousand by $5.75 and everything over that by $5.00. This is what I have came up with, but it is not working. Then if it is $1,000,000.00 or more I would like it to say "call for quote" any suggestions would be appreciated. Thank you =IF(B30<1,"0",IF(B30<=100000,SUM(B30/1000)*5.75,IF(B30=100001,AND(B30<1000000),SUM(B30-100000)/1000*5+575))) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if and or ifthen?
Bob didn't see the cost per thousand. Change his formula to this
=MIN(100000,A1)*0.00575+MAX(0,A1-100000)*0.005 and it doesn't include the "over 1 million" clause, but it gives the same results for numbers below 1 million. You can include the above formula with the following custom number format to account for the over 1 million clause. [=5075]"Call for quote";General "Pam" wrote: Thanks, the "toppers" one works. The min/max doesn't return the correct value, but perhaps I have to change the min/max values? I have never used that function, but the formula looks much cleaner. "Toppers" wrote: Try: =IF(B30<1,0,IF(B30<=100000,(B30/1000)*5.75,IF(AND(B30=100001,B30<1000000),(B30-100000)/1000*5+575,"Call for quote"))) HTH "Pam" wrote: I have a formulat that is not working. Basically I want a formula that will take up to $100,000 and multiply the cost per thousand by $5.75 if the amount is over 100,000 to $1,000,000 I want it to take the first 100,000 and times it's cost per thousand by $5.75 and everything over that by $5.00. This is what I have came up with, but it is not working. Then if it is $1,000,000.00 or more I would like it to say "call for quote" any suggestions would be appreciated. Thank you =IF(B30<1,"0",IF(B30<=100000,SUM(B30/1000)*5.75,IF(B30=100001,AND(B30<1000000),SUM(B30-100000)/1000*5+575))) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if and or ifthen?
Thanks! You people are AMAZING
"Sloth" wrote: Bob didn't see the cost per thousand. Change his formula to this =MIN(100000,A1)*0.00575+MAX(0,A1-100000)*0.005 and it doesn't include the "over 1 million" clause, but it gives the same results for numbers below 1 million. You can include the above formula with the following custom number format to account for the over 1 million clause. [=5075]"Call for quote";General "Pam" wrote: Thanks, the "toppers" one works. The min/max doesn't return the correct value, but perhaps I have to change the min/max values? I have never used that function, but the formula looks much cleaner. "Toppers" wrote: Try: =IF(B30<1,0,IF(B30<=100000,(B30/1000)*5.75,IF(AND(B30=100001,B30<1000000),(B30-100000)/1000*5+575,"Call for quote"))) HTH "Pam" wrote: I have a formulat that is not working. Basically I want a formula that will take up to $100,000 and multiply the cost per thousand by $5.75 if the amount is over 100,000 to $1,000,000 I want it to take the first 100,000 and times it's cost per thousand by $5.75 and everything over that by $5.00. This is what I have came up with, but it is not working. Then if it is $1,000,000.00 or more I would like it to say "call for quote" any suggestions would be appreciated. Thank you =IF(B30<1,"0",IF(B30<=100000,SUM(B30/1000)*5.75,IF(B30=100001,AND(B30<1000000),SUM(B30-100000)/1000*5+575))) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if and or ifthen?
small point, I would leave the rates as given, and divide by 1000 at the
end (One less operation as well) =(MIN(100000,A1)*5.75+MAX(0,A1-100000)*5)/1000 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Sloth" wrote in message ... Bob didn't see the cost per thousand. Change his formula to this =MIN(100000,A1)*0.00575+MAX(0,A1-100000)*0.005 and it doesn't include the "over 1 million" clause, but it gives the same results for numbers below 1 million. You can include the above formula with the following custom number format to account for the over 1 million clause. [=5075]"Call for quote";General "Pam" wrote: Thanks, the "toppers" one works. The min/max doesn't return the correct value, but perhaps I have to change the min/max values? I have never used that function, but the formula looks much cleaner. "Toppers" wrote: Try: =IF(B30<1,0,IF(B30<=100000,(B30/1000)*5.75,IF(AND(B30=100001,B30<1000000),( B30-100000)/1000*5+575,"Call for quote"))) HTH "Pam" wrote: I have a formulat that is not working. Basically I want a formula that will take up to $100,000 and multiply the cost per thousand by $5.75 if the amount is over 100,000 to $1,000,000 I want it to take the first 100,000 and times it's cost per thousand by $5.75 and everything over that by $5.00. This is what I have came up with, but it is not working. Then if it is $1,000,000.00 or more I would like it to say "call for quote" any suggestions would be appreciated. Thank you =IF(B30<1,"0",IF(B30<=100000,SUM(B30/1000)*5.75,IF(B30=100001,AND(B30<10000 00),SUM(B30-100000)/1000*5+575))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do you fill a cell with a color based on IFTHEN formula? | Excel Worksheet Functions | |||
Linear Mode
