I trying to find the max of the number in range FY12District that have the criteria of 1 in range FY12UnDaysOut using range names. The ranges are the same size. I'm getting a #N/A error.
1 15
1 10
2 5
1 6
2 20
Answer would be 15
=MAX(IF(FY12District=1,FY12UnDaysOut)) using Ctrl, Shift, Enter
Last edited by shuddle; 10-18-2011 at 09:41 AM.
Check if the data is actually numeric.
Select FY12District and go to Data|Text to Columns and click Finish.
if that doesn't work yet, do the same for the FY12UnDaysOut range.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
They are both numeric
It will not allow me to accesss the text to column feature.
Is it a protected workbook or shared?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
It is protected but I have it unprotected while I am working in it.
I take that back. The page where the data is located is protected. The page where the formula is is unprotected.
Can't you unprotect the data sheet and try the text to columns... for the formula to return #N/A there is no match.. and it may be because the numbers in the data sheet are text.
Try this to confirm:
=MAX(IF(FY12District="1",FY12UnDaysOut)) CSE confirmed
does that give a result?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I see that you are hitting Ctrl-Shift-Enter (CSE), but does the formula actually have the curly brackets around it once you hit CSE? Looks like it should work...
Pauley
{Never mind - now see that you had some proctected cells}
Last edited by Pauleyb; 10-17-2011 at 02:56 PM.
I got it to work by having it reference a cell rather than entering the number 1 and hitting Enter rather than Ctrl-Shift-Enter.
Now the mystery is....I can take the exact same formula, copied or typed in, and put it in another cell and it bring back a 0 value. I've had several people look at it and it is exact. I even went back to a previous version and started from scratch. Ever had this happen before???
It is an array formula and must be CSE entered to work properly.. it is only coincidence that it "seemed" to work with just ENTER for the first one...
Can you attach a sample of the workbook at all? Or a downsized one with confidential stuff (if any) removed?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I have attached an example. The cell in on the Master Summary tab. E14
The reason why you are getting the error is that FY12District range has errors in it itself, which are hidden through conditional formatting.
You have to get rid of the errors or change the formula to ignore them.
e.g. =MAX(IF(ISNUMBER(FY12District),IF(FY12District=1,FY12UnDaysOut)))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
That worked. Thanks so much for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks