need an if formula, but not sure where it goes

Hey Guys,

First time here and hoping someone could Help me:

=INDEX($G$4:$G$416,SMALL(IF($E$4:$E$416=(VLOOKUP(TODAY(),SUMMARY,26,FALSE)),ROW($G$4:$G$416)-ROW($G$4)+1),COLUMNS('DATA@'!$DQ$6:DQ6)))

I am a Youtube and google Excel user (No REAL Knowledge) and i have come up with this formula which really works well for me. However, i want to add 1 more condition and that is where i am stumped.

I want it to Only Give me back infomation if Row F has "Monday" in it.

So something like:

=if($f$4:$f$416= "Monday",INDEX($G$4:$G$416,SMALL(IF($E$4:$E$416=(VLOOKUP(TODAY(),SUMMARY,26,FALSE)),ROW($G$4:$G$416)-ROW($G$4)+1),COLUMNS('DATA@'!$DQ$6:DQ6)))

But that doesn't work.
Can someone PLEASE HELP. GOOGLE HAS FAILED ME!

Thanks,

Daniel

think i have attached sample

Try

=SUMPRODUCT(($C$2:$C$29)*(TEXT($A$2:$A$29,"dddd")=F5)*($B$2:$B$29=$G$5))/SUMPRODUCT(--(TEXT($A$2:$A$29,"dddd")=F5)*($B$2:$B$29=$G$5))

Change F5/G5 to "day"/"weather" cell e.g. F11/G11 for Tuesday

Hi John, Does work, thank you very much.

Would you be able to explain to me how breifly, if you don't mind.

Thanks Heaps!!!!

Daniel

Originally Posted by JohnTopley

Try

=SUMPRODUCT(($C$2:$C$29)*(TEXT($A$2:$A$29,"dddd")=F5)*($B$2:$B$29=$G$5))/SUMPRODUCT(--(TEXT($A$2:$A$29,"dddd")=F5)*($B$2:$B$29=$G$5))

Change F5/G5 to "day"/"weather" cell e.g. F11/G11 for Tuesday

Alright, heres another couple of questions.
Say Row A has 3 years of dates in it. But i only want to see the most recent 4 results?

Or i had another column that had temperature in it and i only want to see averages of days that have the weather number and also feel in a range of tempreture? So like all mondays with weather # 3 and tempreture between 15 and 18.? Possible?

Thanks heaps for your help. Truely appreciate it!

=SUMPRODUCT(($C$2:$C$29)*(TEXT($A$2:$A$29,"dddd")=F5)*($B$2:$B$29=$G$5))/SUMPRODUCT(--(TEXT($A$2:$A$29,"dddd")=F5)*($B$2:$B$29=$G$5))


First the asterisk "*" is the AND condition ("+" is used as OR condition although not applicable here)

The highlighted BLUE is:


($C$2:$C$29) - your sales figures

(TEXT($A$2:$A$29,"dddd")=F5) Check the "day" of the dates using TEXT to convert to DDDD e.g. Monday and compare with your selected day in F5 and will return a TRUE or FALSE condition

($B$2:$B$29=$G$5) check if the "Weather" values match the value in G5 againing returning a true or FALSE condition

The * (Multiplication) "converts" any TRUE of FALSE to 1 or 0 respectively so we multiple each element of the ranges e.g C2*A2*B2, C3*A3*B3 .... so if both A2 and B2 are true we will get C2 as a result: if either are 0 we will get 0. This is the PRODUCT part of the formula: we then SUM these results - hence SUMPRODUCT!

Putting these together in the same SUMPRODUCT: SUM the Sales where Date=Selected day AND Weather=Selected whether so this gives is total sales for this Day/Weather combination.


The other SUMPRODUCT (in RED) simple gives a COUNT of the combination of selected Days and Weather

Dividing first SUMPRODUCT (Total Sales) by second SUMPRODUCT( count of Sales) gives us the average.

Hope this makes sense.

You will find other explanations on the web and possibly YouTUbe videos.

See the attached decision to a named range "weekday".

Re your other questions:

Say Row A has 3 years of dates in it. But i only want to see the most recent 4 results?

You will need to a add further test to check if the dates are >= the 4th largest (dates in Excel are held as numbers so the latest date in a range will be the largest)

($A$2:$A$29>=Large($A$2:$A$29,4)

=SUMPRODUCT(($C$2:$C$29)*(TEXT($A$2:$A$29,"dddd")=F5)*($B$2:$B$29=$G$5)*($A$2:$A$29>=Large($A$2:$A$29,4))/SUMPRODUCT(--(TEXT($A$2:$A$29,"dddd")=F5)*($B$2:$B$29=$G$5)*($A$2:$A$29>=Large($A$2:$A$29,4))

This should find the 4 most recent Mondays with Weather=3 (based on your posted file) : untested!

So like all mondays with weather # 3 and temperature between 15 and 18.?

e.g

($D$2:$D$29>=H1)*($D$2:$D$29<=H2)

D are your temperatures

H1 = Lowest temp
H2 = highest temp

Add into BOTH SUMPRODUCTS

John You are a legend!

Thanks mate,

Fantastic explanation and great problem Solving. Thanks Heaps!!!

Yep, Tried doing this multiple ways, however once large divides its self it doesn't like it. Is there another way or am I doing this wrong. Help PLEASE: