Another confusing formula...

[sharkey]

Help! Totally lost here...For this equation I'm trying to take a value (N9) and subtract the amount of that value that is pending deactivation(I9), then from that amount I want to subtract values prior to applying a discount (1-$O$3), and then adding those values back and then multiplying by the term (PIVOTS.LOOKUPS!$AT$4). At this point anything with an iferror attached is just going through as a zero...


=IF(A9="","",((N9-I9)-((IFERROR(VLOOKUP(A9,PIVOTS.LOOKUPS!AZ:BA,2,FALSE),0))-((IFERROR(VLOOKUP(A9,PIVOTS.LOOKUPS!

BC:BD,2,FALSE),0))-((IFERROR(VLOOKUP(A9,PIVOTS.LOOKUPS

!BF:BG,2,FALSE),0))))))*(1-$O$3)+((IFERROR(VLOOKUP(A9,PIVOTS.LOOKUPS!AZ:BA,2,FALSE),0))+((IFERROR(VLOOKUP(A9,PIVOTS.LOOKUPS!BC:BD,2,FALSE),0))+((IFERROR(VLOOKUP(A9,PIVOTS.LOOKUPS!BF:BG,2,FALSE),0))))))*PIVOTS.LOOKUPS!$AT$4

 

[sharkey]

To clarify, the tables I'm looking up may or may not contain relevant data (hence the IFERROR statement).

 

[Faseeh]

Hi Sharkey,


Can you upload the sample file??


Faseeh

 

[NARAYANK991]

Hi ,


If we list down what you want do , we have the following :


1. Subtract I9 from N9 ( N9-I9 )


2. Subtract some values from (1) above


3. Apply a discount ( 1-$O$3 ) on the result of (1) - (2)


4. Add those values back to the result in (3)


5. Multiply (4) by the term (PIVOTS.LOOKUPS!$AT$4).


Now , you say :


At this point anything with an iferror attached is just going through as a zero...


Is the IFERROR to be applied only on the final result , or is it to be applied on every term which can generate an error ?


If we separate the terms you mention , we have the following :


temp1 : IFERROR(VLOOKUP(A9,PIVOTS.LOOKUPS!AZ:BA,2,FALSE),0)


temp2 : IFERROR(VLOOKUP(A9,PIVOTS.LOOKUPS!BC:BD,2,FALSE),0)


temp3 : IFERROR(VLOOKUP(A9,PIVOTS.LOOKUPS!BF:BG,2,FALSE),0)


The final formula should be :


=IF(A9="","",((N9-I9-temp1-temp2-temp3)*(1-$O$3)+temp1+temp2+temp3)*PIVOTS.LOOKUPS!$AT$4)


The change is that the multiplication by PIVOTS.LOOKUPS!$AT$4 is happening within the IF statement rather than outside ; if you have an IF statement such as :


=IF(A9="","",x)*y


then when A9 is "" , the result of the IF statement is "" , which when multiplied by y results in a #VALUE! error.


Narayan