#num!...

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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,801
Office Version
  1. 2010
Platform
  1. Windows
I don't understand what you mean, sorry...:confused:
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,921
Office Version
  1. 365
Platform
  1. 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,801
Office Version
  1. 2010
Platform
  1. 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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,823
Messages
5,655,479
Members
418,205
Latest member
jacc99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top