Try this.
its an array formula and I could have hardcoded the arrays into the formula but I've set it up in the attached spreadsheet to show how it might be easier to use.
=SUMPRODUCT(C2:C5,IF(IF(E2-A2:A5>B2:B5-A2:A5,B2:B5-A2:A5,E2-A2:A5)<0,0,IF(E2-A2:A5>B2:B5-A2:A5,B2:B5-A2:A5,E2-A2:A5)))*E1
Can you check a few examples to make sure I've understood the logic and got the arithmetic right? thanks
And we won soccer too!
Happy with my advice? Click on the * reputation button below
Congrats!!!!Originally Posted by Crooza
Try this.
its an array formula and I could have hardcoded the arrays into the formula but I've set it up in the attached spreadsheet to show how it might be easier to use.=SUMPRODUCT(C2:C5,IF(IF(E2-A2:A5>B2:B5-A2:A5,B2:B5-A2:A5,E2-A2:A5)<0,0,IF(E2-A2:A5>B2:B5-A2:A5,B2:B5-A2:A5,E2-A2:A5)))*E1
Can you check a few examples to make sure I've understood the logic and got the arithmetic right? thanks
And we won soccer too!
I just tried a handful of scenarios and it seems to work for me. Laying it out was a great way for me to learn how the formula works, thank you for that. How would I hardcore the array?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks