MODE IF using INDIRECT

[brooksc29]

what type of formula would I need to say

If G2:U80 = "FB"
and the cell below FB says "1"
then find the MODE for the cell below "1"

typically the data is arranged like
row 1 FB
row 2 1
row 3 6
row 4 FB
row 5 1
row 6 A
row 7 FB
row 8 1
row 9 6

the data goes across the row from col G:U in the same pattern

thank you very much for your help! this is a confusing project!

for more information on this project I can either email you my work or you can read another thread where I haven't received a response yet.
http://www.mrexcel.com/forum/showthread.php?t=545394

 

[Aladin Akyurek]

Just taking up 1 column sample you posted...

Control+shift+enter, not just enter:

=MODE(IF(G1:G7="FB",IF(G2:G8=1,G3:G9)))

 

[brooksc29]

OK, what if the data is on the entire sheet from G2:U80 and every 4 rows starting with row 2 contains FB and every 4 rows starting with row 3 contains 1 and every 4 rows starting with row 3 contains the data I need to find the mode for?

 

[Aladin Akyurek]

OK, what if the data is on the entire sheet from G2:U80 and every 4 rows starting with row 2 contains FB and every 4 rows starting with row 3 contains 1 and every 4 rows starting with row 3 contains the data I need to find the mode for?

Control+shift+enter, not just enter:

=MODE(IF(G2:U78="FB",IF(G3:U79=1,G4:U80)))

Does this help?

 

[brooksc29]

well, two issues with that -

1) in this data set there will also be the number 1 in both rows below FB so will that confuse things?

2) will it count blank cells and if so, will the mode most likely be 0 if there are a lot of blank cells?

this is why I thought using indirect to reference the cell directly below FB then the cell 2 directly below FB would be the best way.

I'm not sure how to write it with INDIRECT though.

 

[Aladin Akyurek]

well, two issues with that -

1) in this data set there will also be the number 1 in both rows below FB so will that confuse things?

No.

2) will it count blank cells and if so, will the mode most likely be 0 if there are a lot of blank cells?

IF would read empty cells as 0. The following adjustment will avoid the consequences of that...

Control+shift+enter, not just enter:

=MODE(IF(G2:U78="FB",IF(G3:U79=1,IF(ISNUMBER(G4:U80),G4:U80))))

this is why I thought using indirect to reference the cell directly below FB then the cell 2 directly below FB would be the best way.

I'm not sure how to write it with INDIRECT though.

I don't think INDIRECT is needed here.

 

[brooksc29]

well, i entered {=MODE(IF(G2:U78="FB",IF(G3:U79=1,IF(ISNUMBER(G4:U80),G4:U80))))}

and the result was 0. Unfortunately, there are no zeros for any data entered...

confusing b/c that formula makes complete sense to me...

 

[brooksc29]

would you be comfortable with me emailing you a worksheet so you can see it easier?

 

[brooksc29]

hmm, I actually think I got it to work... not sure why it wasn't the first time.

I do have an additional question to this formula.

If I need to base the search contingent upon a name in column C - how would I write that in?

say {=MODE(IF(G2:U78="FB",IF(G3:U79=1,IF(ISNUMBER(G4:U80),G4:U80))))} if the name in column C = cell W2

 

[Aladin Akyurek]

hmm, I actually think I got it to work... not sure why it wasn't the first time.

I do have an additional question to this formula.

If I need to base the search contingent upon a name in column C - how would I write that in?

say {=MODE(IF(G2:U78="FB",IF(G3:U79=1,IF(ISNUMBER(G4:U80),G4:U80))))} if the name in column C = cell W2

Would you post the first 10 rows from C along with the corresponding 10 rows from G?