Snasui Excel Forum

Need u r help for excel formula in attached file.
---------
For 1st column Ans...
Need to work with averageifs function, give criteria of date to from bottom latest current date to last 4 months with date functions.
How to work with date criteria for from latest date to last 4 months
part i dont know.
For 2nd Column Ans....
Use same 1st column formula,
Only averageifs unction need to give work to mention Amount if TT01 has 3times greater than 1st column average amount.

Thanks in Advance

Please provide the example result of each line to understand your target value.

Here is a new attached with expected result

...

Hi guy,

Still not quite sure about the criteria and what are the exact answers that you need.

Try the easy way by using the Pivot Table to solve that.

E3
=MONTH(A3)

X5
=AVERAGEIF(P5:V5,"<>0")


Y5
=LOOKUP(2,1/(K5:V5>X5*3),K5:V5)


G3..Hxxx
=INDEX(X$5:X$35,MATCH($F3,$J$5:$J$35,0))


I hope this may help you.

Norkaz

without pivot table,can any possible solutions with formula?

Hi Guy,

Before taking the formula to solve that, please help to confirm the answers I provided were the correct ones that you need.

Norkaz

...

If the results are still the same as I provided by using the Pivot Table, try this with the formula to solve that.


G3
=SUMPRODUCT($C$3:$C$366*(F3=$B$3:$B$366)*(MONTH($A$3:$A$366)>5))/7

H3
=IFERROR(LOOKUP(2,1/(((F3=$B$3:$B$366)*$C$3:$C$366)>=G3*3),$C$3:$C$366),0)

Norkaz

Thanks