I am trying to create a mode formula that returns the mode for a specific range when all criteria is satisfied. So basically I have a long list of data I want the formula to search through all the criteria columns and then calculate the mode for only those corresponding data rows in column N that satisfy my criteria.
I have tried various if formulas and just cant do it!! Anyone got any ideas?
Please Login or Register to view this content.
The equation should be of the form:IF(CONDITIONS,MODE(RANGE),"")OR:i.e., if the conditions are met then calc the MODE=IF(MODE(RANGE)=CELL,"y","n")i.e., if the MODE=some value, then do something else
Ben Van Johnson
Thanks but I may not have explained myself well. The idea would be to use the conditional formula below BUT I don't want the calculation to return the mode for the entire range I only want the mode for the numbers that meet my conditions.
for example say I have data in in the range X1:X100 and in that rows 24, 33,34 and 99 have satisfied my criteria I then need the formula to return the mode for the range X24, X33, X34 and X99.
The formula below would return the mode for the entire range which is not what I am trying to do.
Please Login or Register to view this content.
Hi
Give this a try:
=Mode(if(B5:B8219=B5, if(C5:C8219=C5, if(D5:D8219=D5, if(E5:E8219=E5, N5:N8219)))))
Confirmed with Ctrl+Shift+Enter.
Richard
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks