LinkBack URL
About LinkBacks
Hi Gurus,
How to set Min, Max and Average for a range but only count on those cells with numbers without consider #N/A or any non number character?
min, max,average.JPG
Hi Gurus,
How to set Min, Max and Average for a range but only count on those cells with numbers without consider #N/A or any non number character?
min, max,average.JPG
Last edited by SiewLee_81; 07-17-2019 at 11:21 PM.
You can use the standard functions in Excel if you replace the #N/A error messages with a blank.
=IFNA(your_formula,"")
Then simply:
=MIN(B2:D2)
=MAX(B2:D2)
=AVERAGE(B2:D2)
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.
or try below formula
e2=AGGREGATE(5,6,B2:D2)
f2=AGGREGATE(4,6,B2:D2)
g2=AGGREGATE(1,6,B2:D2)
try the above, copy and paste towards down
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Please Login or Register to view this content.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Or without replacing the #N/A errors.
Min =AGGREGATE(15,6,B2:D2,1)
Max =AGGREGATE(14,6,B2:D2,1)
Average =AVERAGEIF(B2:D2,"<>#N/A",B2:D2)
Hi Ali,
Yes, I got think at this way but I wish to have 1 formula because I don't have extra column in my report. May I know how to combine this 2 formula into 1 line?
Where did the OP ask for a sum???Originally Posted by :) Sixthsense :)
Please Login or Register to view this content.
You don't need an extra column.
REPLACE the formulae in B2 to D4 with this:
=IFNA(your_formula,"")
Hm... Then replace Sumif() with Countif(), AverageIf() etc.,Where did the OP ask for a sum???
Better to answer the question directly, don't you think?
Yes..i tried but the result is blank
=IFNA(MIN(B2:D2),"")
Attachment 632723
SiewLee - if you want to attach your workbook, I can advise you better about updating current formulae to simplify your tasks.
What you have just done is not what I suggested!!!
Please attach the workbook.
Hi Samba,
Thanks and can you explain to me the coding meaning?
5,6
4,6
1,6
The OP asked for min and max, minifs and maxifs are not supported in excel 2016.Hm... Then replace Sumif() with Countif(), AverageIf() etc.,
Hi Ali,
Where I can attached my working file? I dont find an attachment icon to add in
Below formula's supported in lower version of Excel too..
Array Formula's - Requires CTRL+SHIFT+ENTER
Please Login or Register to view this content.Do you need any other variation?Please Login or Register to view this content.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Why don't you try some of the other suggestions first? When you see that they work, then you would realise that you don't need to attach the file!
The codes in Samba's formula are all explained in detail in excel's built in help.
You will need to explain to the OP how to enter these as array-entered formulae.
hi Jason,
Thanks for your input and Samba's way is work but I wish to study more way from other expert.
What does this mean? I hope you are not suggesting that anybody posting here should know that already. Please remember that many (most?) or our members are novices.
Add more emoticon's, since I need a clap emoticon in this situation
Yes, they do work, Jason, absolutely!When you see that they work, then you would realise that you don't need to attach the file!
However, I am trying to help the OP to refine their workbook so that workarounds to deal with error messages aren't necessary. If the OP wishes to take me up on this, that's their decision. If they don't, that's fine, too.
It means I am afraid, because explaining something in words in step by step in which I am not comfortable
That formula's are given for jason.b75 and I think he is not a novice
I can answer questions but I cannot take classes about how to work with excelPlease remember that many (most?) or our members are novices.
Sixthsense - you can use the array formula canned message.
Gee, thanks. You could have at least given me some good ones. Your formulas look like they were written by a novice, anyone who knows their stuff would leave the false arguments empty!That formula's are given for jason.b75 and I think he is not a novice
SiewLee - are you OK uploading the workbook? I posted instructions in post #17.
=MIN(IF(ISNUMBER(A1:C1),A1:C1,9^9))
Assume the values are like 45, 500, 300 800.
What will be the result if we leave the last argument as empty? It will result 0. So the Min() will also return the minimum value as 0. But 45 is the minimum value in the given example.
Who is the novice? You Decide.
Negative Rep.bmp
Last edited by :) Sixthsense :); 07-17-2019 at 06:07 AM.
Gentleman - please take this argument away from this thread - it isn't helping the OP in the slightest. Thank you.
I just given my explanation based on the criticism raised in my given formula solution. I have not discussed anything which is not related to this thread.
Hope you understand
How do you fit 45, 500, 300, 800 (4 values) into A1:C1 (3 cells).
Arrays that don't fit aside, with the last argument empty, the formula would return the correct MIN value of 45, not 0 as you say.
The only time it would return 0 with the last argument left empty would be if all of the cells were empty, or if the actual min value was 0. Yours would return 387420489 if all cells were empty.
Similarly, your MAX formula would incorrectly return 0 if there were no positive values in the data source, where leaving the last argument empty would return the correct result.Decision made
Jason & Sixthsense
The personal attacks on each other (name-calling) are UNACCEPTABLE. If this spat continues, infractions will be served on both sides. You have been warned.
Thanks for the correction
I haven't seen any name calling, Ali! Merely a debate on skill level.
Between you, you seem to have have managed to drive the OP away.
The purpose of this thread is to provide the OP with a solution, not debate each other's skill levels. No more such discussions here, please - take it to PM if you wish to continue.
Yes. I am having to attach file. I manged to find the "attachment" icon but can proceed to browse my fileSiewLee - are you OK uploading the workbook? I posted instructions in post #17.
Untitled.jpg
Can anyone advise the next step to make sure my attachment uploaded because I can't find button to post it.Yes. I am having to attach file. I manged to find the "attachment" icon but can proceed to browse my file
Attachment 632756
Attachment 632761
The icon doesn't work, you need to click the 'Go Advanced' button in the bottom right corner of the reply box (which it looks like you have done in that image), then scroll down and look for the text link that says 'Manage Attachments' which will be on the left and lower down the screen.
yes..i use the go advanced, managed attachments. what is then ext steps after I upload my excel file? I don't see any "submit" button.
Attachment 632769
Take a look here, formulae similar to those guiven by Jason a couple (or more!!) of screenfulls ago.
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
After uploading, it's close and save reply.... I upload my excel file? I don't see any "submit" button.
after uploading, I click the "close this window". Not sure the attachment successfully post or not?
min,max,average formula.xlsx
Please try
E2
=AGGREGATE(5,6,$B2:$D2)
F2
=AGGREGATE(4,6,$B2:$D2)
G2
=AGGREGATE(1,6,$B2:$D2)
Hi all Experts,
Thank you so much and my issue settled. I will mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks