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

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

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

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

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

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

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

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

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