if and or ifthen?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pam - 17 Aug 2006 16:26 GMT

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

Reply to this Message

Bob Phillips - 17 Aug 2006 16:35 GMT

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

Signature

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
[quoted text clipped - 6 lines]
> 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 to this Message

Toppers - 17 Aug 2006 16:38 GMT

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

> I have a formulat that is not working.
> Basically I want a formula that will take up to $100,000 and multiply the
[quoted text clipped - 8 lines]
>
> =IF(B30<1,"0",IF(B30<=100000,SUM(B30/1000)*5.75,IF(B30>=100001,AND(B30<1000000),SUM(B30-100000)/1000*5+575)))

Reply to this Message

Pam - 17 Aug 2006 16:45 GMT

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.

> Try:
>
[quoted text clipped - 14 lines]
> >
> > =IF(B30<1,"0",IF(B30<=100000,SUM(B30/1000)*5.75,IF(B30>=100001,AND(B30<1000000),SUM(B30-100000)/1000*5+575)))

Reply to this Message

Sloth - 17 Aug 2006 17:03 GMT

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
[quoted text clipped - 18 lines]
> > >
> > > =IF(B30<1,"0",IF(B30<=100000,SUM(B30/1000)*5.75,IF(B30>=100001,AND(B30<1000000),SUM(B30-100000)/1000*5+575)))

Reply to this Message

Pam - 17 Aug 2006 17:11 GMT

Thanks! You people are AMAZING

> Bob didn't see the cost per thousand. Change his formula to this
>
[quoted text clipped - 28 lines]
> > > >
> > > > =IF(B30<1,"0",IF(B30<=100000,SUM(B30/1000)*5.75,IF(B30>=100001,AND(B30<1000000),SUM(B30-100000)/1000*5+575)))

Reply to this Message

Bob Phillips - 17 Aug 2006 18:58 GMT

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

Signature

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
>
[quoted text clipped - 11 lines]
> >
> > > 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
> > >
[quoted text clipped - 8 lines]
> > > > 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 to this Message

Toppers - 17 Aug 2006 16:51 GMT

To allow for costs per 1000:

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

> Try:
>
[quoted text clipped - 14 lines]
> >
> > =IF(B30<1,"0",IF(B30<=100000,SUM(B30/1000)*5.75,IF(B30>=100001,AND(B30<1000000),SUM(B30-100000)/1000*5+575)))

Reply to this Message

Toppers - 17 Aug 2006 16:41 GMT

.or...

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

Reply to this Message