LinkBack URL
About LinkBacks
Hi All,
I have a formula on the attached sheet on page 'Felicity' in cell V5 - for some reason the formula doesn't work but it's just a copy of the formulas in column V. I can't see why it doesn't work.
Can anyone help?
Hi All,
I have a formula on the attached sheet on page 'Felicity' in cell V5 - for some reason the formula doesn't work but it's just a copy of the formulas in column V. I can't see why it doesn't work.
Can anyone help?
Last edited by AliGW; 11-14-2017 at 11:53 AM. Reason: Improved thread title.
Your formulas are working fine.
There is no mode for Nov-17 or for Jan-00.
What value did you expect?
I was expecting to see a 3, as the most common occurring number in column M.
Why were you? There are a 3 and a 4 in the November range, so no most frequently occurring number (just one of each).
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
You will get an error because there is only single occurence of any value (3,5). Add another 3 to November and you get result of 3
Note that by the definition of mode, the mode is the value that appears the most in a list of values.
If none of the values repeat, there is no mode.
I see, thank you. Is there a way of getting around this? Even if there was just a single occurrence I could still do with working!
Try this in V2:
=IFERROR(MODE(IF(L$2:L$402=U2,M$2:M$402)),INDEX(M$2:M$402,MATCH(1,IF((L$2:L$402=U2)*(M$2:M$402<>""),1),0))) Ctrl Shift Enter
If there is no mode (but there is a number), the formula will return the first matched number. If there is no mode and there is no number, the formula will return #N/A.
I see! Thank you, that's helped enormously.
You're welcome. Thanks for the rep!
Another option is to duplicate the results so that MODE always finds a result when there are any numbers present, e.g. with this array formula
=MODE(IF($L$2:$L$402=U2,IF($M$2:$M$402<>"",$M$2:$M$402*{1,1})))
confirm with CTRL+SHIFT+ENTER
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks