#num!...

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,343
Isnt it the column part of your INDEX?
Arent H, HT,A and FT all anges that are 1 column wide?
Yet your formula in I2, J2 and downthose columns, ie INDEX(H... ROWS(I$2,I2))
if its greater than 1 produces a #NUM error
 

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,782
Office Version
2010
Platform
Windows
I don't understand what you mean, sorry...:confused:
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,614
Office Version
2019
Platform
Windows
Isnt it the column part of your INDEX?
Arent H, HT,A and FT all anges that are 1 column wide?
Yet your formula in I2, J2 and downthose columns, ie INDEX(H... ROWS(I$2,I2))
if its greater than 1 produces a #NUM error

This needs to count the occurrences of each value in HT, the original formula will work for repeated instances of the largest value in the array (try changing other values in column B to 169 and this will become more obvious), once the value in HT to be evaluated changes the formula fails.

These revied formulae should do the trick, note that I've removed = from the H2 formula as it was blanking the last result.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">169</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">100</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">75</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>H2</TD><TD>=IF(ROWS(H$2:H2)>$H$1,"",LARGE($B$2:$B$35,ROWS(H$2:H2)))</TD></TR><TR><TD>I2</TD><TD>{=IF(ROWS(I$2:I2)<=$H$1,INDEX(H,SMALL(IF(HT=$H2,ROW(HT)-ROW($B$2)+1),COUNTIF($H$2:H2,H2))),"")}</TD></TR><TR><TD>J2</TD><TD>{=IF(ROWS(J$2:J2)<=$H$1,INDEX(A,SMALL(IF(HT=$H2,ROW(HT)-ROW($B$2)+1),COUNTIF($H$2:H2,H2))),"")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>
 

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,782
Office Version
2010
Platform
Windows
That is strange, I was thinking about people who have helped me with this project last night and I did think I haven't seen Jason for a while...

Thanks for that, I hope things are well with you. :)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,010
Messages
5,508,774
Members
408,692
Latest member
OptimalKR

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top