Is there an easier way?

  • Thread starter Thread starter Alexey
  • Start date Start date
A

Alexey

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$5000,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 said:
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)
))
))
)
Click to expand...

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$5000)
 
Hi Can anyone advise if there is an easier and less complicated way to
achieve the following :
Click to expand...

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
 
Back
Top