Formula Help

[Tennisguuy]

I have the follow formula in cell D26

=IFERROR(IF(AND(C8=0,C14=0,C16=0),"",IF(AND(C8>0,C14>0,C16>0),TEXT(SUM(C26-C6-C10-C12-C18-C20-C22-C24),"#,##0")&""&"(ECPO)"&"-"&TEXT(SUM((C26-C6-C10-C12-C18-C20-C22-C24)/C26),"##.00")*100&""&"%")&"-"&ROUND(SUM(C8,C14,C16)/(D8/1000),3)),IF(C26>0,TEXT(SUM(C26-C6-C10-C12-C18-C20-C22-C24),"#,##0")&""&"(ECPO)"&"-"&TEXT(SUM((C26-C6-C10-C12-C18-C20-C22-C24)/C26),"##.00")*100&""&"%",""))

I have the following values in cells

C6= 123,000 D6=2,407,900
C8=10,000 D8=6,800,000
C10=248,000 D10=32

There is a formula in cell C26 which sums the values in C6-C10-C12-C18-C20-C22-C24. Therefore the value in C26 is 381,000

The formula is return False-1.471. The 1.471 is the valuein C8/D8. If I remove the value in D8 it returns 10,000(ECPO)-3% which is correct. Where I am confused since there is no value in C14 or C16 is showing as false which is correct but then does the ROUND(SUM(C8,C14,C16)/(D8/1000),3)) to get the rate of 1.471. I thought since the skip this entire formula since it is false. I am confused why its doing the &ROUND(SUM(C8,C14,C16)/(D8/1000),3)) since that formula is false.

 

[igold]

At a quick glance, if it helps, you are missing a "Value If False" argument for the IF statement highlighted in red...

=IFERROR(IF(AND(C8=0,C14=0,C16=0),"",IF(AND(C8>0,C14>0,C16>0),TEXT(SUM(C26-C6-C10-C12-C18-C20-C22-C24),"#,##0")&""&"(ECPO)"&"-"&TEXT(SUM((C26-C6-C10-C12-C18-C20-C22-C24)/C26),"##.00")*100&""&"%")&"-"&ROUND(SUM(C8,C14,C16)/(D8/1000),3)),IF(C26>0,TEXT(SUM(C26-C6-C10-C12-C18-C20-C22-C24),"#,##0")&""&"(ECPO)"&"-"&TEXT(SUM((C26-C6-C10-C12-C18-C20-C22-C24)/C26),"##.00")*100&""&"%",""))

So it is adding the ampersand to false.

 

[Tennisguuy]

I thought it if was false it should move to the next part of the formula which is IF(C26>0,TEXT(SUM(C26-C6-C10-C12-C18-C20-C22-C24),"#,##0")&""&"(ECPO)"&"-"&TEXT(SUM((C26-C6-C10-C12-C18-C20-C22-C24)/C26),"##.00")*100&""&"%" and which is what I wanted it to do if I value was missing to move to the next part. It does do that if I don't have a value in cell D8 it will move to the next part. Which is why I am confused why if there is no value in D8 or C14 or C16 it moves to the third part of the formula but as soon as I enter a value in D8 it stop as second formula and says false.

 

[igold]

As far as Excel is concerned, it thinks the formula ends at the False. It takes the return of "False" and adds that to your "&" and what follows which is

"-"&ROUND(SUM(C8,C14,C16)/(D8/1000),3)) which equals -1.471

Which is where Excel then ends the formula. The rest is ignored.

Whatever you want to do with a "Value if False" part should be put in that part of the formula as with any nested if.

 

[Tennisguuy]

I changed it to this and this worked
IFERROR(IF(AND(C8=0,C14=0,C16=0),"",IF(AND(C8>0,C14>0,C16>0),TEXT(SUM(C26-C6-C10-C12-C18-C20-C22-C24),"#,##0")&""&"(ECPO)"&"-"&TEXT(SUM((C26-C6-C10-C12-C18-C20-C22-C24)/C26),"##.00")*100&""&"%"&"-"&ROUND(SUM(C8,C14,C16)/(D8/1000),3),TEXT(SUM(C26-C6-C10-C12-C18-C20-C22-C24),"#,##0")&""&"(ECPO)"&"-"&TEXT(SUM((C26-C6-C10-C12-C18-C20-C22-C24)/C26),"##.00")*100&""&"%")),"")