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="Ex press",D3<240),B29*D3),(IF(OR(D7="Express",D3<420) ,B30*D3),(IF(OR(D7="Express",D3419),B31*D3))))

Thanks in advance for your help.

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" wrote:

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="Ex press",D3<240),B29*D3),(IF(OR(D7="Express",D3<420) ,B30*D3),(IF(OR(D7="Express",D3419),B31*D3))))

Thanks in advance for your help.

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

"Toppers" wrote:

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" wrote:

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="Ex press",D3<240),B29*D3),(IF(OR(D7="Express",D3<420) ,B30*D3),(IF(OR(D7="Express",D3419),B31*D3))))

Thanks in advance for your help.

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