if and or ifthen?

[Guest]

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)))

 

[Bob Phillips]

=MIN(100000,A1)*5.75+MAX(0,A1-100000)*5

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

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)))

 

[Guest]

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

 

[Guest]

..or...

=IF(B30<1,"0",IF(B30<=100000,(B30/1000)*5.75,IF(B30<1000000,(B30-100000)/1000*5+575,"Call for quote")))

 

[Guest]

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.

 

[Guest]

To allow for costs per 1000:

=MIN(100000,B30)*0.00575+MAX(0,(B30-100000))*0.005

 

[Guest]

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

 

[Guest]

Thanks! You people are AMAZING

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

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.

 

[Bob Phillips]

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)

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

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.

=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)))