How to simply this Excel formula

Solved/Closed
Rufus - Updated on Oct 24, 2019 at 07:22 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Oct 28, 2019 at 12:24 PM
Is there a way to make this formula simpler?

=IF($K9=0,"",SUM($L9*$L11)/R9+IF($K9=0,"",SUM($M9*$M11)/$R9+IF($K9=0,"",SUM($N9*$N11)/$R9+IF($K9=0,"",SUM($O9*$O11)/$R9+IF($K9=0,"",SUM($P9*$P11)/$R9+IF($K9=0,"",SUM($Q9*$Q11)/$R9))))))

1 reply

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Updated on Oct 24, 2019 at 11:47 AM
Hi Rufus,

Sure, how about this:
=IF($K9<>0,($L9*$L11+$M9*$M11+$N9*$N11+$O9*$O11+$P9*$P11+$Q9*$Q11)/$R9,"")

Or even shorter:
=IF($K9=0,"",SUM($L9:$Q9*$L11:$Q11)/$R9)
NOTE: This is an array formula and needs to be confirmed using Ctrl+Shift+Enter. When done correctly the formula will be enclosed by curly brackets { }.

Best regards,
Trowa

1
Thanks Trowa, That worked great. However if for instance Q9 and Q11 is blank it returns a #VALUE!. I need the formula to ignore the blanks. I am using Microsoft Office 2019.
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Oct 28, 2019 at 12:24 PM
Hi Rufus,

Not sure why that is, as it doesn't happen to me:

Formula1:


Formula2:


NOTE: I'm using a Dutch version:
ALS translates as IF
SOM as SUM
; as ,

Do you maybe notice a difference between my test setup and your data?

Best regards,
Trowa
0