Help with formulas

[harwookf]

I hope somebody can help me with a formula with conditions.

A number is entered in D3 and either "Express" or "Direct" is put in D7. If
the number is <120 and also Express, I want it to then multiply another
field, say B28 with D3. However, if the number is >119 < 240 and says
Express, then is needs to multiply B29 with D3. The same needs to be done if
Direct is entered, except this multiplies B33 with D3, etc. There a couple
of other variables in the number, but the basic format is the same.

On top of this, I need to be able to deduct 5% or 7.5%, etc.

I started off with a formula which started to work, but after a couple of
IFs it will no longer work.

=IF(AND(D7="Express",D3<120),B28*D3),(IF(OR(D7="Express",D3<240),B29*D3),(IF(OR(D7="Express",D3<420),B30*D3),(IF(OR(D7="Express",D3>419),B31*D3))))

Thanks in advance for your help.

 

[Toppers]

try:

=IF(D7="Express",IF(D3<120,D3*B28,IF(D3<240,D3*B29,IF(D3<420,D3*B30,D3*B31))),IF(D3<120,D3*B33,IF(D3<240,D3*B34,IF(D3<420,D3*B35,D3*B36))))

Assumes D7 is either "Express" OR "Direct"

HTH

 

[harwookf]

Wow, it worked - many thanks.

Just one additional point. I also need to deduct a percentage from a couple
of the cells the formula needs to go into, basically to take long term
contracts into account.
I have the discounts in B44 (5%) and B45 (10%) for 3 and 5 year terms.

Cheers

 

[Greg Wilson]

This will return 0 if neither "Express" or "Direct" are entered in D7:

=D3*IF(D7="Express",IF(D3<120, B28,IF(D3<240,B29,IF(D3<420, B30,
B31))),IF(D7="Direct",IF(D3<120,B33,IF(D3<240,B34,IF(D3<420,B35,B36))),0))

You can then take the return value (if it's greater than 0) and deduct the
percentages.

Greg