Using =MODE and Ignoring "0"

[Titchn]

Hi,
This is my first post and I'm really sorry if someone has already asked about this.

I need a formula that will find the mode from a range of numbers but ignore the 0's. I have worked out the below array formula which seems to work.

{=IF(MODE(IF(N18:P25>0,N18:P25))=1,"Low",IF(MODE(IF(N18:P25>0,N18:P25))=2,"Secure",IF(MODE(IF(N18:P25>0,N18:P25))=3,"High")))}

The only thing is, if only one cell contains a number other than 0 (eg. 1,2 or 3) then it returns #N/A. Ideally i'd like it to show the appropriate response for the number that is in the number range. For example - if one cell shows the number 2 and the rest are 0's, i'd like it to show Secure(the response for 2) rather than #N/A.

If anybody has any suggestion it would be greatly appreciated. Sorry if it's a silly question and doesnt make any sense!

[DonkeyOte]

What you use will in part depend on reality...

ie if your range contains no duplicated numbers what should the output ? should it be based on Min / Max of the unique numbers ?

Please Login or Register  to view this content.

The above will ape MODE function in terms of how ties are handled and will return the lowest unique number where no MODE exists.

[shg]

Welcome to the forum.

Perhaps {=CHOOSE(MODE(IF(N18:P25>0, N18:P25)), "Low", "Secure","High")}

The formula will error if the mode is not 1, 2, or 3.

[Titchn]

Hi Shg and DonkeyOte,

Thanks for reponding so quickly. You'll have to forgive me if I dont understand an responses straight away as I've taught myself! I've tried both formulas and they are both great, but even though there is a single 2 in my data range its still coming back with #N/A or "No Data". If possible I was hoping to get it to ignore all the 0 and come back with Secure - as the only number in the range other than the 0's is 2. Not sure if this is possible tho!
The only numbers that will appear in the cells within the range are the number 1-3 (and 0) as these are differnet levels. I wanted my formula to find the level that occurs the most and then input a word dependent on the most re-occuring level.
Sorry if I'm not making any sense or answering the questions properly.

[DonkeyOte]

I modified mine I'm afraid around 15 minutes after having initially posted it as I had failed to add an Array to the MIN to account for 0 being excluded

The revised version should do what you want (I believe)... apologies for confusion caused.

[daddylonglegs]

This would give you "Secure" with a single 2....

=INDEX({"low","secure","high"},MATCH(MAX(COUNTIF(N18:P25,{1,2,3})),COUNTIF(N18:P25,{1,2,3}),0))

confirmed with CTRL+SHIFT+ENTER

[DonkeyOte]

v. nice dll !

[shg]

Very nice, DLL. Does it really need CSE?

[Titchn]

Thats great guys! Thanks for your help. Today was the first time I have used array so am just getting the hang of it. I dont think I could have figured it out myself. If it's not too much of a pain would you be able to explain the different parts in the above formula so I might be able to use some of it in other formulas in the future. Thanks again!

[Titchn]

Would anyone be able to advise if there is something I can slip into

=INDEX({"low","secure","high"},MATCH(MAX(COUNTIF(N18:P25,{1,2,3})),COUNTIF(N18:P25,{1,2,3}),0))

confirmed with CTRL+SHIFT+ENTER

so that the Cell remains blank if the result is neither 1, 2 or 3?
Thanks

[DonkeyOte]

I think both shg & I were leaving it to dll but he's not presently online so in his absence...

If you use that approach then

Please Login or Register  to view this content.

as outlined earlier by shg, no need for CTRL + SHIFT + ENTER with the above

the only thing with the above formula is that it will return the first unique number found > 0 should there be no duplicates... my earlier (uglier) array will mimic MODE in the sense that it will return the lowest value in the case of a tie regardless of which is the first unique number.
(though the result_vector values need to be altered - ie Secure rather than Medium)

[Titchn]

Thanks DokeyOte. That seems to have done the trick!
You mentioned in your 2nd post that you needed to add an Array to the MIN to account for 0 being excluded... What would that look like? Sorry to be a pain, just trying to get my head around it so I can use it for something in the future!

[DonkeyOte]

I've given that ago and even tho all the numbers in the range are 0 - its still coming back as "Low".

Could you post a sample - if all values are 0 it should return Null.

You mentioned in your second post that you needed to add an Array to the MIN to account for 0 being excluded... once the array is added what would the formula look like?

The Array was added to the post at the time of the edit (ie why I edited it) - ie what is in post # 2 is the "finished" article so to speak... that unlike the above is an Array and so does require CTRL + SHIFT + ENTER.

[Titchn]

Cool, thanks for that.

Sorry, my mistake I didnt copy it into the right place and got slightly distracted by my 4 year old niece! Have done it again and it works perfect. Thanks for all your help on this

[daddylonglegs]

as outlined earlier by shg, no need for CTRL + SHIFT + ENTER with the above

Oops!, yes, correct no CSE.....

the only thing with the above formula is that it will return the first unique number found > 0 should there be no duplicates... my earlier (uglier) array will mimic MODE in the sense that it will return the lowest value in the case of a tie regardless of which is the first unique number.

Isn't that the other way round, Luke?

My suggestion always returns the lowest value with any tie, I believe. Yours will return the lowest when there's no more than one of each but in a tie with more than 1 of each it'll favour the first value found (first giving preference to low row number over low column number)

[DonkeyOte]

Sorry, yes, you're quite right - I had not noticed that MODE returned first number - I had incorrectly assumed it was returning the lowest (based on my obviously limited testing!). Furthermore I didn't follow the logic of your formula correctly.

All in all - a bad day at the office

Apologies to OP for confusion caused on my part.

(If I can I'd like to blame the oversight on multi-tasking as I'm trying to reconcile the cricket clubs accounts... and unless I've made a similar oversight someone's been dipping their fingers...(I'm hedging my bets it's my error))