| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Value that exists more than all other values
Good Afternoon,
I'm having trouble trying to write a function that returns the value in a given row that exists more than all other values in that same range. Please see the below example. A1 = Apple B1 = Banana C1 = Orange D1 = Apple E1 (Formula) = This cell should return Apple because Apple is listed in more cells than all other fruits. Thank you, Adam |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Value that exists more than all other values
One way
Assuming data in row1 Put in A2, array-enter the formula by pressing CTRL+SHIFT+ENTER: =INDEX(1:1,MATCH(MAX(COUNTIF(1:1,1:1)),COUNTIF(1:1 ,1:1),0)) In the event of any ties in the max occurences, only the "leftmost" item in row1 will be returned -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AdamE" wrote: Good Afternoon, I'm having trouble trying to write a function that returns the value in a given row that exists more than all other values in that same range. Please see the below example. A1 = Apple B1 = Banana C1 = Orange D1 = Apple E1 (Formula) = This cell should return Apple because Apple is listed in more cells than all other fruits. Thank you, Adam |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Value that exists more than all other values
=INDEX(A1:J1,MODE(MATCH(A1:J1&"",A1:J1&"",0)))
"AdamE" wrote: Good Afternoon, I'm having trouble trying to write a function that returns the value in a given row that exists more than all other values in that same range. Please see the below example. A1 = Apple B1 = Banana C1 = Orange D1 = Apple E1 (Formula) = This cell should return Apple because Apple is listed in more cells than all other fruits. Thank you, Adam |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Value that exists more than all other values
Another one:
Array entered**: =INDEX(rng,MODE(MATCH(rng,rng,0))) Will return #N/A if there are empty cells within the rng. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "AdamE" wrote in message ... Good Afternoon, I'm having trouble trying to write a function that returns the value in a given row that exists more than all other values in that same range. Please see the below example. A1 = Apple B1 = Banana C1 = Orange D1 = Apple E1 (Formula) = This cell should return Apple because Apple is listed in more cells than all other fruits. Thank you, Adam |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Value that exists more than all other values
Hi Max,
This works great! However, there is one issue I am running into. Whenever there are more empty cells than there are values it is returning the empty cell. How should this function be modified to ignore blank cells? Thank you for your help, it is much appreciated! "Max" wrote: One way Assuming data in row1 Put in A2, array-enter the formula by pressing CTRL+SHIFT+ENTER: =INDEX(1:1,MATCH(MAX(COUNTIF(1:1,1:1)),COUNTIF(1:1 ,1:1),0)) In the event of any ties in the max occurences, only the "leftmost" item in row1 will be returned -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AdamE" wrote: Good Afternoon, I'm having trouble trying to write a function that returns the value in a given row that exists more than all other values in that same range. Please see the below example. A1 = Apple B1 = Banana C1 = Orange D1 = Apple E1 (Formula) = This cell should return Apple because Apple is listed in more cells than all other fruits. Thank you, Adam |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Value that exists more than all other values
I just re-tried it again here, with values & blank cells interspersed in row1.
The earlier expression returns correctly, it ignores blank cells ? (Ensure the cells are really blank, clear these with the delete key) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AdamE" wrote: Hi Max, This works great! However, there is one issue I am running into. Whenever there are more empty cells than there are values it is returning the empty cell. How should this function be modified to ignore blank cells? Thank you for your help, it is much appreciated! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Value that exists more than all other values
That formula will ignore *empty* cells but will not ignore cells with
formula blanks (""). Also, it will evaluate empty cells and cells that contain formula blanks ("") as being the same. For example, if you had 5 apples, 1 formula blank and 5 empty cells the formula would return the formula blank as the mode value. Try this (array entered): =INDEX(rng,MODE(IF(rng<"",MATCH(rng,rng,0)))) -- Biff Microsoft Excel MVP "Max" wrote in message ... I just re-tried it again here, with values & blank cells interspersed in row1. The earlier expression returns correctly, it ignores blank cells ? (Ensure the cells are really blank, clear these with the delete key) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AdamE" wrote: Hi Max, This works great! However, there is one issue I am running into. Whenever there are more empty cells than there are values it is returning the empty cell. How should this function be modified to ignore blank cells? Thank you for your help, it is much appreciated! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Value that exists more than all other values
I was just clarifying the OP's statement:
Whenever there are more empty cells than there are values it is returning the empty cell. ... and I did have this line added for emphasis in the response: (Ensure the cells are really blank, clear these with the delete key) Once a cell contains a formula, of course it's no longer empty (yeah, I know that) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "T. Valko" wrote in message ... That formula will ignore *empty* cells but will not ignore cells with formula blanks (""). Also, it will evaluate empty cells and cells that contain formula blanks ("") as being the same. For example, if you had 5 apples, 1 formula blank and 5 empty cells the formula would return the formula blank as the mode value. Try this (array entered): =INDEX(rng,MODE(IF(rng<"",MATCH(rng,rng,0)))) -- Biff Microsoft Excel MVP |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Value that exists more than all other values
Thank you Biff,
The additional "if" function fixed my problem as the cells are in fact empty. You guys are awesome! Thank you all so much for your help. Sincerely, Adam "T. Valko" wrote: That formula will ignore *empty* cells but will not ignore cells with formula blanks (""). Also, it will evaluate empty cells and cells that contain formula blanks ("") as being the same. For example, if you had 5 apples, 1 formula blank and 5 empty cells the formula would return the formula blank as the mode value. Try this (array entered): =INDEX(rng,MODE(IF(rng<"",MATCH(rng,rng,0)))) -- Biff Microsoft Excel MVP "Max" wrote in message ... I just re-tried it again here, with values & blank cells interspersed in row1. The earlier expression returns correctly, it ignores blank cells ? (Ensure the cells are really blank, clear these with the delete key) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AdamE" wrote: Hi Max, This works great! However, there is one issue I am running into. Whenever there are more empty cells than there are values it is returning the empty cell. How should this function be modified to ignore blank cells? Thank you for your help, it is much appreciated! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Value that exists more than all other values
"AdamE" wrote
.. The additional "if" function fixed my problem as the cells are in fact empty. I'm puzzled by your assertion ... as the cells are in fact empty. As stated in my response, the earlier array expression: =INDEX(1:1,MATCH(MAX(COUNTIF(1:1,1:1)),COUNTIF(1:1 ,1:1),0)) will return correctly, it ignores blank cells Even Biff himself acknowledges that in his interjection opener: That formula will ignore *empty* cells .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Check if text exists within a cell range and return logical vaule - possible? | Excel Discussion (Misc queries) | |||
| Return adjacent cell if conditional formatting exists. | Excel Worksheet Functions | |||
| Match return #NA ...though item exists! | Excel Discussion (Misc queries) | |||
| Search multiple values to return single values | Excel Worksheet Functions | |||
| check if reference exists, then return its value or return 0 | Excel Worksheet Functions | |||
Linear Mode
