LinkBack URL
About LinkBacks
Please see attached workbook.
This requires brain of a genius to solve. I need a formula for column L, so that I can get rid of the manual helper columns of D and E.
Thank you so very much.
SUMPRODUCT help needed to calculate rate (requires brain of a genius)
Please see attached workbook.
This requires brain of a genius to solve. I need a formula for column L, so that I can get rid of the manual helper columns of D and E.
Thank you so very much.
Perhaps this array formula:
=SUMPRODUCT(IF($A$2:$A$19=$A$1:$A$18,IF($A$2:$A$19=$A$3:$A$20,$B$1:$B$18-$B$2:$B$19,$B$1:$B$18-I3),J3-$B$2:$B$19)*($A$2:$A$19=H3)*($C$2:$C$19))
Rory
similar variant, also requiring array entry:
L3:
=SUM(TEXT((IF(($B$1:$B$18<$J3)*($A$1:$A$18=$H3),$B$1:$B$18,$J3)-IF($B$2:$B$19>$I3,$B$2:$B$19,$I3))*($A$2:$A$19=$H3),"0;\0;0;")*($C$2:$C$19))
confirmed with CTRL + SHIFT + ENTER
copied down to L4 etc
SUMPRODUCT with CSE makes me want to scream...
ARRAY formula in M2 then copy down
How ARRAY formula is entered
Please Login or Register to view this content.
Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
words cannot express how grateful to all of you.
You guys are great! indeed genius
Thank you so very much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks