Hi Can anyone advise if there is an easier and less complicated way to
achieve the following :

=SUM($C127+(SUM(IF(ISNA(VLOOKUP($A124,MISDATA!$A$3 :$B$5000,2,0)),0,VLOOKUP($A124,MISDATA!$A$3:$B$500 0,2,0))-(IF(ISNA(VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0)), 0,VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0))))))

Thanks

A

"Alexey" wrote...
Hi *Can anyone advise if there is an easier and less complicated way to
achieve the following :

[reformatted]
=SUM(
$C127
+(SUM(
IF(
ISNA(VLOOKUP($A124,MISDATA!$A$3:$B$5000,2,0)),
0,
VLOOKUP($A124,MISDATA!$A$3:$B$5000,2,0)
)-(IF(
ISNA(VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0)),
0,
VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0)
))
))
)

Eliminate the unnecessary SUM calls. SUM(a+b) can ALWAYS be replaced
by a+b.

Eliminate the unnecessary parentheses.

If there could be multiple instances of the A124 value in MISDATA!
A3:A5000 or MISDATA!D3:D5000, your formula would use only the first
(topmost) one found, and there's no simpler approach than what you're
using. In which case you could reduce your formula to

=$C127
+IF(ISNA(VLOOKUP($A124,MISDATA!$A$3:$B$5000,2,0)), 0,
VLOOKUP($A124,MISDATA!$A$3:$B$5000,2,0))
-IF(ISNA(VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0)),0 ,
VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0))

OTOH, if there'd only be at most one instance of the A124 value in
both MISDATA!A3:A5000 or MISDATA!D3:D5000, you could use SUMIF. For
example,

=$C127
+SUMIF(MISDATA!$A$3:$A$5000,$A124,MISDATA!$B$3:$B$ 5000)
-SUMIF(MISDATA!$D$3:$D$5000,$A124,MISDATA!$E$3:$E$5 000)

Yes there is.

=SUM($C127+(SUM(function1)-(function2)))
can be expressed merely as
=$C127+function1-function2

Having got rid of 2 unnecessary SUM functions and few unnecessary pairs of
parentheses, I will let someone else tackle the next stage of the
simplification.
--
David Biddulph

"Alexey" wrote in message
...
Hi Can anyone advise if there is an easier and less complicated way to
achieve the following :

=SUM($C127+(SUM(IF(ISNA(VLOOKUP($A124,MISDATA!$A$3 :$B$5000,2,0)),0,VLOOKUP($A124,MISDATA!$A$3:$B$500 0,2,0))-(IF(ISNA(VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0)), 0,VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0))))))

Thanks

A

Thanks both

A

"Alexey" wrote in message
...
Hi Can anyone advise if there is an easier and less complicated way to
achieve the following :

=SUM($C127+(SUM(IF(ISNA(VLOOKUP($A124,MISDATA!$A$3 :$B$5000,2,0)),0,VLOOKUP($A124,MISDATA!$A$3:$B$500 0,2,0))-(IF(ISNA(VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0)), 0,VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0))))))

Thanks

A

Hi Can anyone advise if there is an easier and less complicated way to

achieve the following :


Hi. I like to use Range Names for Tables.
If any error is ok, perhaps with Excel 2007...
(not tested!)

=SUM(
$C127,
IFERROR(VLOOKUP($A124,TblAB,2,0),0),
-IFERROR(VLOOKUP($A124,TblDE,2,0),0)
)

--
HTH :)
Dana DeLouis


"Alexey" wrote in message ...

Hi Can anyone advise if there is an easier and less complicated way to
achieve the following :

=SUM($C127+(SUM(IF(ISNA(VLOOKUP($A124,MISDATA!$A$3 :$B$5000,2,0)),0,VLOOKUP($A124,MISDATA!$A$3:$B$500 0,2,0))-(IF(ISNA(VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0)), 0,VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0))))))

Thanks

A