Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula too long using IF OR
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))))) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula too long using IF OR
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 "Amanda" wrote: 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))))) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula too long using IF OR
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))) In article , Amanda wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula too long | Excel Worksheet Functions | |||
Formula Too Long | Excel Worksheet Functions | |||
formula too long! | Excel Discussion (Misc queries) | |||
formula too long | Excel Worksheet Functions | |||
Formula Too Long | Excel Discussion (Misc queries) |