Help with CSE formula (MODE-IF)

[tom_19]

Hello -

I'm not sure what I'm trying to do is possible. I've attached a small spreadsheet. It contains 2 columns with data and a CSE formula in cell D1. What I'm trying to do is get the mode given a condition. The CSE formula works as is, but what I'm hoping to do is allow for some flexibility in the range.

For instance, I have 9 rows of data right now, but I'm trying to integrate this into a program I'm writing, and the number of rows constantly changes so the range can't be a set number of cells. In cell D3 is what I'm hoping to achieve, but it seems when I try to use entire columns I get a "#NUM!" error.

Any thoughts or suggestions?

Thanks!

[NBVC]

Create a named range, through Formulas tab | Define Name.

Call it DataRange and assign the formula:

=Sheet1!$A$1:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$A:$A))

then your Mode function becomes:

=MODE(IF(INDEX(DataRange,0,1)="TX",INDEX(DataRange,0,2)))

[Ron Coderre]

Your profile indicates you have Excel 2010.

If that's true, you could convert your data range to a table.
Then you could use an array-formula like this to calculate the MODE while automatically adjusting for changes in the data range
=MODE(IF(Table1[[#All],[State]]="TX",Table1[[#All],[Amount]]))

(see the attached workbook)

Is that something you can work with?

[tom_19]

Thanks for the help and quick replies! I tried NBVC's suggestion and it worked beautifully!

Ron, I am using Excel 2010 at home, but my office (who will be using my program) runs Excel 2003 still. For this reason, I imagine I should probably stick with the DataRange suggestion over your Table suggestion for compatibility issues, right?

[Ron Coderre]

Thanks for the help and quick replies! I tried NBVC's suggestion and it worked beautifully!

Ron, I am using Excel 2010 at home, but my office (who will be using my program) runs Excel 2003 still. For this reason, I imagine I should probably stick with the DataRange suggestion over your Table suggestion for compatibility issues, right?

Absolutely, yes!

[tom_19]

Hmm, wondering if I could trouble either one of you for further clarification on this issue? After your replies, I quickly tested this and had no trouble, but now I've gotten a little further and am running into an issue.

I've attached a workbook called "test file." I stripped out all of my data except what I'm working with to do this CSE formula. On the 'Variables' worksheet, I'm trying to write 3 separate CSE formulas to find the modes based on the data on the 'Aged Invoices' worksheet. I have 3 accounts I'm doing this for. The first 2 (510300 and 10020037) are working fine, but the last 1 (10021991) is giving me an "#N/A" error, and I'm not sure why. It should be returning a result of 30.

[NBVC]

Since you are starting the the numbering at A4 instead of A1, we need to adjust the Named range formula a bit, to offset the header rows... so change the DataRange formula to:


='Aged Invoices'!$A$4:INDEX('Aged Invoices'!$I:$I,COUNTA('Aged Invoices'!$A:$A)+2)

[tom_19]

That did it! Thanks!!