Hi everyone,
I have a worksheet where I have a formula looking for target numbers (in B column) from the Master sheet, and then returning the number right above it or just before it. It does this for all the instances of that target number found in the B column of Master list.
Here is screenshot of Sheet1
B column only contains the target number, 1 to 10, no formulas
C column contains formula: COUNTIF(B1Table,B3), copied down C3 to C12. B1table is the B column of Master sheet.
D3 thru XX Columns contain the formula, copied across and down:
IF($C3>=COLUMNS($F3:F3),SUMPRODUCT(--(LARGE((Master!$B$2:Master!$B$1999=$B3)*ROW(Master!$B$2:Master!$B$1999),$C3-COLUMNS($F3:F3)+1)>ROW(Master!$B$2:Master!$B$1999)),--(ROW(Master!$B$2:Master!$B$1999)>=LARGE((Master!$B$2:Master!$B$1999=$B3)*ROW(Master!$B$2:Master!$B$1999),$C3-COLUMNS($F3:F3)+1)-$B1),Master!$B$2:Master!$B$1999),"0")
for reference here is what formula in V12 looks like:
=IF($C12>=COLUMNS($D12:V12),SUMPRODUCT(--(LARGE((Master!$B$3:Master!$B$2000=$B12)*ROW(Master!$B$3:Master!$B$2000),$C12-COLUMNS($D12:V12)+1)>ROW(Master!$B$3:Master!$B$2000)),--(ROW(Master!$B$3:Master!$B$2000)>=LARGE((Master!$B$3:Master!$B$2000=$B12)*ROW(Master!$B$3:Master!$B$2000),$C12-COLUMNS($D12:V12)+1)-$B$1),Master!$B$3:Master!$B$2000),"0")
Here a screenshot of Master sheet:
I'm trying to come up with a similar formula, that does the same thing, but instead of returning the number directly above the target number it returns the number directly below it. Make it Sheet2.
I've tried and tried to modify the above formula to get it to do that, but I just mess things up. I can't do it. Can someone help me figure this out.
It would be awesome if you could also explain how the above formula works.. I can't understand how it all works together to accomplish what it does.
Thank you so much in advance for your help.
Dave
I have a worksheet where I have a formula looking for target numbers (in B column) from the Master sheet, and then returning the number right above it or just before it. It does this for all the instances of that target number found in the B column of Master list.
Here is screenshot of Sheet1
B column only contains the target number, 1 to 10, no formulas
C column contains formula: COUNTIF(B1Table,B3), copied down C3 to C12. B1table is the B column of Master sheet.
D3 thru XX Columns contain the formula, copied across and down:
IF($C3>=COLUMNS($F3:F3),SUMPRODUCT(--(LARGE((Master!$B$2:Master!$B$1999=$B3)*ROW(Master!$B$2:Master!$B$1999),$C3-COLUMNS($F3:F3)+1)>ROW(Master!$B$2:Master!$B$1999)),--(ROW(Master!$B$2:Master!$B$1999)>=LARGE((Master!$B$2:Master!$B$1999=$B3)*ROW(Master!$B$2:Master!$B$1999),$C3-COLUMNS($F3:F3)+1)-$B1),Master!$B$2:Master!$B$1999),"0")
for reference here is what formula in V12 looks like:
=IF($C12>=COLUMNS($D12:V12),SUMPRODUCT(--(LARGE((Master!$B$3:Master!$B$2000=$B12)*ROW(Master!$B$3:Master!$B$2000),$C12-COLUMNS($D12:V12)+1)>ROW(Master!$B$3:Master!$B$2000)),--(ROW(Master!$B$3:Master!$B$2000)>=LARGE((Master!$B$3:Master!$B$2000=$B12)*ROW(Master!$B$3:Master!$B$2000),$C12-COLUMNS($D12:V12)+1)-$B$1),Master!$B$3:Master!$B$2000),"0")
Here a screenshot of Master sheet:
I'm trying to come up with a similar formula, that does the same thing, but instead of returning the number directly above the target number it returns the number directly below it. Make it Sheet2.
I've tried and tried to modify the above formula to get it to do that, but I just mess things up. I can't do it. Can someone help me figure this out.
It would be awesome if you could also explain how the above formula works.. I can't understand how it all works together to accomplish what it does.
Thank you so much in advance for your help.
Dave