Formula too long using IF OR

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Amanda - 27 Feb 2007 20:39 GMT

I have been using an IF OR formula to calculate prices based on different
texts in column 4. Depending on the text the mark up is then calcualated in
one of 3 different ways. My problem is now that when I have tried to add a
new text into the formula I get a message saying "formula too long" Please
does anyone know a way around this?

Here is my formula:

=IF(OR(D8="Nacional Desv.",D8="Mov. Vodafone _D.C*",D8="Tarificación
Adicional",D8="Conecta",D8="Núm. Vodafone Desv.",D8="Fijo
nacional",D8="Nacional _D.C*",D8="Especial",D8="Especial
Voz",D8="Nacional",D8="Llam. Interna",D8="Núm. Vodafone",D8="Mov.
Vodafone",D8="Movil no
Vf.",D8="Provincial"),(((H8-0.12)*1.4)+0.3),IF(OR(D8="Mensaje Corto",D8="SMS
Nacional",D8="Sms extranj-España",D8="Realiz. Roaming",D8="Sms
extranj-Internac",D8="Contest.Gratuito",D8="Realiz. extranj.",D8="Sms
extranj-extranj",D8="Premium",D8="Sms enviado.roaming",D8="Realiz.Vodafone
World",D8="Recib. Extranj",D8="Recib. Passport",D8="SMS Real. VF
World",D8="Realiz Passport-Fijo Nac",D8="Realiz. Passport-extranj",D8="SMS
Roaming Real.",D8="SMS Internc.",D8="Roaming Recb.",D8="SMS Vodafone",D8="VF
World",D8="Realiz. Passport-Vodafone",D8="Realiz. Passport-mov
nac",D8="Mensaje Multimedia",D8="Realiz.
Passport-internac"),(H8*1.4),IF(D8="Internacional",(((H8-0.3)*1.4+0.3)))))

Reply to this Message

Toppers - 27 Feb 2007 20:56 GMT

Perhaps have a table of all your texts (D8 entries) and a corresponding value
of 1, 2 or 3 to represent the 3 calculations.

Use VLOOKUP to return a value (1,2,3) which determines what formula to use.

HTH

> I have been using an IF OR formula to calculate prices based on different
> texts in column 4. Depending on the text the mark up is then calcualated in
[quoted text clipped - 19 lines]
> nac",D8="Mensaje Multimedia",D8="Realiz.
> Passport-internac"),(H8*1.4),IF(D8="Internacional",(((H8-0.3)*1.4+0.3)))))

Reply to this Message

JE McGimpsey - 27 Feb 2007 21:11 GMT

I'd use a couple of helper columns. Perhaps

J1: Nacional Desv.
J2: Mov. Vodafone _D.C*
...
J15: Provincial

K1: Mensaje Corto
K2: SMS Nacional
...
K25: Realiz. Passport-internac

L1: Internacional

Then use the formula:

=IF(COUNTIF(J:J,D8)>0,(H8-0.12)*1.4+0.3, IF(COUNTIF(K:K,D8)>0,
H8*1.4, IF(COUNTIF(L:L, D8)>0, (H8-0.3)*1.4+0.3)))

Reply to this Message