Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 84
Default Problem With formula

I have built this formula in cell Y4 which is used to calculate prices:

=IF(X4="",IF(ISNA(L4),"",IF(G4<"",L4*(1-G4),IF(LEFT(H4,5)="1M0ER",L4*0.
4,IF(OR(LEFT(H4,5)="1C050",LEFT(H4,5)="1H00E",H4=" 1HACCH1MEL000H000E",H4
="1HACCH2CNT000H000E",H4="1HACCH5CNV000H000E",H4=" 1HACCH6SBI000H000E",H4
="1HACCHIMELH0003",H4="1HACCH2CNTH0003",H4="1HACCH 5CNVH0003",H4="1HACCH6
SBIH0003"),L4*0.38*0.95,IF(OR(LEFT(H4,5)="1F0VE",L EFT(H4,5)="1FACC",LEFT
(H4,5)="1F0VT",LEFT(H4,5)="1FRMP",LEFT(H4,5)="1F0F V",LEFT(H4,5)="1FRIC")
,L4*0.45*0.95,IF(OR(E4=1,E4=3),L4*0.4*0.9,"")))))) )

If I insert something more in line No. 8, Excel complains. See the
"IF(E4=2)L4*0.4*0.95" shown below on line No. 8. Excel keeps on
telling me that it found a problem with this formula and to click the
Insert Function on the Formula tab to fix it. I cannot figure out what
the problem is... Have I perhaps reached the max number of characters
in a formula?

=IF(X4="",IF(ISNA(L4),"",IF(G4<"",L4*(1-G4),IF(LEFT(H4,5)="1M0ER",L4*0.
4,IF(OR(LEFT(H4,5)="1C050",LEFT(H4,5)="1H00E",H4=" 1HACCH1MEL000H000E",H4
="1HACCH2CNT000H000E",H4="1HACCH5CNV000H000E",H4=" 1HACCH6SBI000H000E",H4
="1HACCHIMELH0003",H4="1HACCH2CNTH0003",H4="1HACCH 5CNVH0003",H4="1HACCH6
SBIH0003"),L4*0.38*0.95,IF(OR(LEFT(H4,5)="1F0VE",L EFT(H4,5)="1FACC",LEFT
(H4,5)="1F0VT",LEFT(H4,5)="1FRMP",LEFT(H4,5)="1F0F V",LEFT(H4,5)="1FRIC")
,L4*0.45*0.95,IF(OR(E4=1,E4=3),L4*0.4*0.9,IF(E4=2) ,L4*0.4*0.95,"")))))))

--
tb
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Problem With formula

Hi,

Am Mon, 30 Jan 2017 18:14:42 +0000 (UTC) schrieb tb:

=IF(X4="",IF(ISNA(L4),"",IF(G4<"",L4*(1-G4),IF(LEFT(H4,5)="1M0ER",L4*0.
4,IF(OR(LEFT(H4,5)="1C050",LEFT(H4,5)="1H00E",H4=" 1HACCH1MEL000H000E",H4
="1HACCH2CNT000H000E",H4="1HACCH5CNV000H000E",H4=" 1HACCH6SBI000H000E",H4
="1HACCHIMELH0003",H4="1HACCH2CNTH0003",H4="1HACCH 5CNVH0003",H4="1HACCH6
SBIH0003"),L4*0.38*0.95,IF(OR(LEFT(H4,5)="1F0VE",L EFT(H4,5)="1FACC",LEFT
(H4,5)="1F0VT",LEFT(H4,5)="1FRMP",LEFT(H4,5)="1F0F V",LEFT(H4,5)="1FRIC")
,L4*0.45*0.95,IF(OR(E4=1,E4=3),L4*0.4*0.9,"")))))) )


try:

=IF(OR(X4="",ISNA(L4)),"",IF(G4<"",L4*(1-G4),IF(LEFT(H4,5)="1M0ER",L4*0.4,IF(OR(OR(LEFT(H4, 5)={"1H00E";"1C050"}),OR(H4={"1HACCH1MEL000H000E"; "1HACCH2CNT000H000E";"1HACCH5CNV000H000E";"1HACCH6 SBI000H000E";"1HACCHIMELH0003";"1HACCH2CNTH0003";" 1HACCH2CNVH0003";"1HACCH6SBIH0003"})),L4*0.38*0.95 ,IF(OR(LEFT(H4,5)={"1F0VE";"1FACC";"1F0VT";"1FRMP" ;"1F0FV";"1FRIC"}),L4*0.45*0.95,IF(OR(E4={1;3}),L4 *0.4*0.9,""))))))


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 84
Default Problem With formula

On 1/30/2017 at 1:58:05 PM Claus Busch wrote:

Hi,

Am Mon, 30 Jan 2017 18:14:42 +0000 (UTC) schrieb tb:

=IF(X4="",IF(ISNA(L4),"",IF(G4<"",L4*(1-G4),IF(LEFT(H4,5)="1M0ER",L
4*0.
4,IF(OR(LEFT(H4,5)="1C050",LEFT(H4,5)="1H00E",H4=" 1HACCH1MEL000H000E
",H4
="1HACCH2CNT000H000E",H4="1HACCH5CNV000H000E",H4=" 1HACCH6SBI000H000E
",H4
="1HACCHIMELH0003",H4="1HACCH2CNTH0003",H4="1HACCH 5CNVH0003",H4="1HA
CCH6
SBIH0003"),L4*0.38*0.95,IF(OR(LEFT(H4,5)="1F0VE",L EFT(H4,5)="1FACC",
LEFT
(H4,5)="1F0VT",LEFT(H4,5)="1FRMP",LEFT(H4,5)="1F0F V",LEFT(H4,5)="1FR
IC") ,L4*0.45*0.95,IF(OR(E4=1,E4=3),L4*0.4*0.9,"")))))) )


try:

=IF(OR(X4="",ISNA(L4)),"",IF(G4<"",L4*(1-G4),IF(LEFT(H4,5)="1M0ER",L4
*0.4,IF(OR(OR(LEFT(H4,5)={"1H00E";"1C050"}),OR(H4= {"1HACCH1MEL000H000E
";"1HACCH2CNT000H000E";"1HACCH5CNV000H000E";"1HACC H6SBI000H000E";"1HAC
CHIMELH0003";"1HACCH2CNTH0003";"1HACCH2CNVH0003";" 1HACCH6SBIH0003"})),
L4*0.38*0.95,IF(OR(LEFT(H4,5)={"1F0VE";"1FACC";"1F 0VT";"1FRMP";"1F0FV"
;"1FRIC"}),L4*0.45*0.95,IF(OR(E4={1;3}),L4*0.4*0.9 ,""))))))


Regards
Claus B.


Thanks, Claus. I will be testing your formula and hopefully everything
will be ok.

--
tb
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
problem with the formula? Kat Excel Worksheet Functions 5 October 6th 09 07:06 PM
formula problem Bernie Excel Worksheet Functions 5 November 24th 08 07:01 PM
Formula Problem gwolfe Excel Worksheet Functions 3 May 2nd 08 06:15 PM
Problem with formula Nleric Excel Discussion (Misc queries) 7 January 4th 07 04:35 PM
formula problem John48 Excel Worksheet Functions 8 August 7th 06 05:17 PM


All times are GMT +1. The time now is 12:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"