I have range of values and need to return min and max values of that range, however, some of the values in the range are #N/A and when I use =min or =max formula I always get #N/A as a result.
How to make excel exclude those #N/A values and pick min and max from those cells, which contain actual numbers only? so, let's say =min formula in G2 would only take values from B2 and C2?
I attached a photo, which may help to describe my issue.
Thanks!
Last edited by kao; 02-28-2018 at 11:56 PM.
Max value:
=AGGREGATE(14,6,range,1)
Min:
=AGGREGATE(15,6,range,1)
why not 4 and 5 instead of 15 and 16?
No reason:
=AGGREGATE(5,6,$A$1:$A$9)
works just as well (note: no ,1 at the end.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
And how to make the cell from which formula aggregate takes the value from highlight itself? So I would be instantly able to see from which range it has been taken?
Cheers!
That would need Conditional Formatting.
Highlight A2:F2, and go to conditional formatting.
Use the formula
=A2=$G2
Last edited by Jonmo1; 03-01-2018 at 10:00 AM.
What does $ besides the range do?Originally Posted by Glenn Kennedy
If I have 250 rows, how would be the easiest way to apply this to all of them?
Instead of highlighting A2:F2, highlight A2:F251
but then here I can only choose once cell (one value), but not a range of cells
In the first step of the CF wizard, choose the option that says “Use a formula to determine...”
and then? what should I put in "format values where this formula is true:" ?
See post # 6
sorry, I still do not get it.
Could you please perform it in the excel file I uploaded?
Thanks!
You need to learn how to do it yourself. Get rid of any attempts you have made so far, then follow these steps in your own file exactly:
1. Select cell A1.
2. Open the CF dialog - create a new rule using the bottom option (formula).
3. In the formula box, type this: =A1=$H1
4. Choose your formatting.
5. Click OK.
6. In the Applies To ... box, type this: =$A$1:$F$34
7. Click OK.
Example attached.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
You can create two rules to do what I think you want.
For each rule, change the formula at step 3 above with:
=A1=MINIFS($A1:$F1,$A1:$F1,"<>#N/A")
or:
=A1=MAXIFS($A1:$F1,$A1:$F1,"<>#N/A")
May not work if you don't have the subscription version, however.
Last edited by AliGW; 03-04-2018 at 06:20 AM.
If you don't have any luck with MINIFS and MAXIFS, use these instead:
=A1=MIN(IF(ISNA($A1:$F1),"",$A1:$F1))
and:
=A1=MAX(IF(ISNA($A1:$F1),"",$A1:$F1))
Last edited by AliGW; 03-04-2018 at 06:38 AM.
AliG, does not work.
The first method kinda works, but it highlights random values (not min and max)
It works here. Did you look at the attachments?
Excel 2016 (Windows) 32 bit
0.11072000 0.05357000 0.000000480 0.00271900
Sheet: Sheet1
Last edited by AliGW; 03-04-2018 at 09:25 AM.
alright, so I have tried it myself by copying some of the values to other sheet and pasting them as values and it actually works!
so the reason why it does not work for me is because the values in my cells are not actually values, but they are rather references (formulas), to be specific, VLOOKUPs from other sheets.
So how to adjust it so it would work in my case?
It should make no difference if the results of the formulae are numbers, but it sounds like they may be text, in which case you would need to tweak the formulae that produce them. Attach a workbook with a sample and one of us will advise.
Here it is, thanks!
You can put +0 at the end of y our vlookups to convert 'Text Numbers' to Real numbers.
=vlookup(....)+0
Jonmo, does not help in this case.
Also, the min/max values are extracted in other columns.
So min values are extracted to column H in this case.
so maybe we can use some other formula so it would take the value from column H, and then look in B:F and highlight the cell which is equal to the one in column H?
We have already done it with a helper column - look back at post #15. But you don't need a helper column.
It's not working because your formula in the CF rule is not correct. The range in your original sample file started at A1. In the new file, it starts at B2, so instead of this:
=A1=MIN(IF(ISNA($A1:$F1),"",$A1:$F1))
you need this:
=B2=MIN(IF(ISNA($B2:$F2),"",$B2:$F2))
I've fixed it for you in the attached.
Excel 2016 (Windows) 32 bit
ABYBTC AMPBTC ARKBTC AURBTC BATBTC BAYBTC BCHBTC 0.11179000 BCPTBTC BLKBTC BLZBTC BSDBTC BTCUSDT BTGBTC BTMBTC CANBTC CANNBTC CLAMBTC CLOAKBTC CMTBTC CTRBTC DASHBTC 0.05411000 DASHUSDT DCRBTC
Sheet: dup
Last edited by AliGW; 03-05-2018 at 02:15 AM.
You are an angel, AliG, thanks!!!
You're welcome - glad we got there in the end.
Last edited by AliGW; 03-05-2018 at 05:59 PM.
getting back to my original post.
is there any way to exclude certain columns from the range?
so it would be someting like this:
=AGGREGATE(5,6,$A$1:$G$1(excluding columns B and F)
what would be the formula for that?
The original question was specifically about cells containing the error message. I suggest you start a new thread with an appropriate title, as this new request is actually quite different.
When you start the new thread, provide a sample file that includes the data and formulae you are currently using, and manually add data to show what you want.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks