Mode and If Statments

B

Brad1982

I tried to search for this and found something similar but it didn't
work for me.

I have a worksheet that has a set of data with dates

B1...11/30/05 E1...5.2
B2...11/30/05 E2...4.18
B3...11/30/05 E3...4.18
B4...12/1/05 E4...4.18
B5...12/2/05 E5...6.7
B6...12/2/05 E6...6.7
B7...12/2/05 E7...6.8

This would be a running list of dates as the year goes on. Sometimes a
date could have as many as 20+ entrys in the B & E columns for a day.
The number of entrys per day is never consistant and I am having
trouble making a mode formula I can just drag down to find the mode for
a specific date and its E entrys.

For instance I want to find the mode for 12/2 but 12/2 might have 18
entrys. Without listing specific cells to find a mode is there a way to
find the mode for E cells with a date in b2 of 12/2.

Thanks a ton, I wasted 4 hours yesterday trying to figure this out!
 
D

Domenic

Try the following formula which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=MODE(IF(A1:A7="2005/12/2"+0,B1:B7))

or

=MODE(IF(A1:A7=D1,B1:B7))

.....where D1 contains the date of interest.

Hope this helps!
 
D

Dave Peterson

=MODE(IF(b1:b99=DATE(2005,12,2),e1:e99)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.))
 
R

Ron Rosenfeld

I tried to search for this and found something similar but it didn't
work for me.

I have a worksheet that has a set of data with dates

B1...11/30/05 E1...5.2
B2...11/30/05 E2...4.18
B3...11/30/05 E3...4.18
B4...12/1/05 E4...4.18
B5...12/2/05 E5...6.7
B6...12/2/05 E6...6.7
B7...12/2/05 E7...6.8

This would be a running list of dates as the year goes on. Sometimes a
date could have as many as 20+ entrys in the B & E columns for a day.
The number of entrys per day is never consistant and I am having
trouble making a mode formula I can just drag down to find the mode for
a specific date and its E entrys.

For instance I want to find the mode for 12/2 but 12/2 might have 18
entrys. Without listing specific cells to find a mode is there a way to
find the mode for E cells with a date in b2 of 12/2.

Thanks a ton, I wasted 4 hours yesterday trying to figure this out!
Click to expand...

You can use an array formula.

For example, NAME your column of Dates: Dates
NAME your column of Data: Data

Use this array formula:

=MODE(IF(Dates=cell_ref,Data))

cell_ref is the cell that contains the date of interest, e.g. 2 Dec 2005.

To enter an array formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.


--ron
 
Top