Adding to a IF(MODE( formula

[brooksc29]

I'd like to add the following to this formula below.

=IFERROR(MODE(IF(PitcherName=IF($X$2="All",PitcherName,$X$2),IF(CatcherName=IF($X$3="All",CatcherName,$X$3),IF(Inning=IF($X$5="All",Inning,$X$5),IF(AllP=IF($X$4="ALL",AllP,$X$4),IF(HitterStance="R",IF(PitchStart=1,IF(ISNUMBER(PitchFinish),IF(PitchStart<>PitchFinish, PitchFinish))))))))),"-")

I need add to the formula so that will only search within the user driven Start and End Dates.

in other words, IF((GameDate>=StartDate)AND(GameDate<=EndDate) then =IFERROR(MODE(IF(PitcherName=IF($X$2="All",PitcherName,$X$2),IF(CatcherName=IF($X$3="All",CatcherName,$X$3),IF(Inning=IF($X$5="All",Inning,$X$5),IF(AllP=IF($X$4="ALL",AllP,$X$4),IF(HitterStance="R",IF(PitchStart=1,IF(ISNUMBER(PitchFinish),IF(PitchStart<>PitchFinish, PitchFinish))))))))),"-")

how can I combine these two?

 

[cgcamal]

Hi brooksc29,

You can combine them as follow:

=IF(AND(GameDate>=StartDate,GameDate<=EndDate),
[COLOR=Blue][B]IFERROR(MODE(IF(PitcherName=IF($X$2="All",PitcherName,$X$2),
IF(CatcherName=IF($X$3="All",CatcherName,$X$3),
IF(Inning=IF($X$5="All",Inning,$X$5),IF(AllP=IF($X$4="ALL",AllP,$X$4),
IF(HitterStance="R",IF(PitchStart=1,IF(ISNUMBER(PitchFinish),
IF(PitchStart<>PitchFinish, PitchFinish))))))))),"-")[/B][/COLOR],[B][COLOR=Red]"Negative part[/COLOR][/B]")

Only is pending you to decide what the formula will do in the negative part (in red) of the main
IF() statement, because the positive part is the large formula (in blue).

Hope it helps.

Regards.

 

[brooksc29]

thank you!

so basically, in the negative part, I am saying if the date range is not between the start date and end date - or if there is no data between the date range?

 

[cgcamal]

Brooks,

I'm not sure if is what you need. If you need to show a "-" when an error occurs or if the condition is out of the range stablished,
you can put in the negative part the same symbol as follow:

=IF(AND(GameDate>=StartDate,GameDate<=EndDate),
[COLOR=Blue][B]IFERROR(MODE(IF(PitcherName=IF($X$2="All",PitcherName,$X$2),
IF(CatcherName=IF($X$3="All",CatcherName,$X$3),
IF(Inning=IF($X$5="All",Inning,$X$5),IF(AllP=IF($X$4="ALL",AllP,$X$4),
IF(HitterStance="R",IF(PitchStart=1,IF(ISNUMBER(PitchFinish),
IF(PitchStart<>PitchFinish, PitchFinish))))))))),"-")[/B][/COLOR],[B][COLOR=Red]"-"[/COLOR][/B])

Regards

 

[brooksc29]

thank you sir...

that's a working formula, but it's giving me "-" and there is definitely a MODE between those dates...

I should be entering as an array, right?

I know it's tough to help when I'm not describing the data all that well.

 

[cgcamal]

You're welcome Brooks,

As MODE may contain a arrays as arguments, enter the formula as an array
and see if works.

Regards

 

[brooksc29]

i'm still getting "-" or no result... not sure why

i went back and cleared the data in NamedRange GameDate... made sure the cells were formatted as date... and entered the dates again... still getting "-"


any ideas?

 

[cgcamal]

Maybe showing some input sample and desired output to get it better.

Regards

 

[Haseeb Avarakkan]

Try,

=IFERROR(MODE(IF(GameDate>=StartDate,IF(GameDate<=EndDate,
IF(PitcherName=IF($X$2="All",PitcherName,$X$2),
IF(CatcherName=IF($X$3="All",CatcherName,$X$3),
IF(Inning=IF($X$5="All",Inning,$X$5),IF(AllP=IF($X$4="ALL",AllP,$X$4),
IF(HitterStance="R",IF(PitchStart=1,IF(ISNUMBER(PitchFinish),
IF(PitchStart<>PitchFinish, PitchFinish))))))))))),"-")

 

[brooksc29]

we have a winner!!!

thank you Haseeb, & Cesar! Much appreciated!