Microsoft Excel - Vlookup? or =IF(), or ??? Not sure how to approach getting the data I need.

Asked By Donald Ross on 29-Apr-13 01:42 PM
Trying again to get this to post,

Currently not able to get the upload manager to refresh and I can not upload my zipped file.  I will try again in a bit to edit this post or have some one reply and I can try to reply to their post.  just a glitch at the moment
Joseph Blas replied to Donald Ross on 29-Apr-13 01:43 PM
Duh !
Donald Ross replied to Joseph Blas on 29-Apr-13 01:45 PM
Fuel & MIS data Log.zip

Thanks for the Duh, Joe it allowed me to add this attachement.

Please see the comment on the Fuel Cost sheet for  my question.

Thanks.


Harry Boughen replied to Donald Ross on 29-Apr-13 10:48 PM
Hi Donald,
Fuel & MIS data Log.zip
I think this will do what you want.  You will just have to fix something up so that January etc get sensible values if there are no purchases.
Regards
Harry
Donald Ross replied to Harry Boughen on 30-Apr-13 12:30 AM
Harry How about this



.=IFERROR(AVERAGEIFS('Fuel Tank Program'!$F22:$F51,'Fuel Tank Program'!$A22:$A51,">="&D1,'Fuel Tank Program'!$A22:$A51,"<"&E1),0)



Problem I am getting is if there is no purchase in Feb then I get nothing I need one more loop to check for a null value and then take the last months value, or the last avail.   we can go 3 months without purchasing MOGAS.








Harry Boughen replied to Donald Ross on 30-Apr-13 04:22 AM
Hello Donald,
I will have a look at your suggestion, but can you clarify that if there are more than one purchase in a month, you want the average price paid?
Regards
Harry
Donald Ross replied to Harry Boughen on 30-Apr-13 08:31 AM
Fuel.zip

Harry,

Here is the file unlocked and with the aforementioned formula.  My issue is the month of Feb we did not purchase any fuel and my formula shows that, what I need is the pervious months cost or in some cases it might go three months,  we still use fuel from the tanks just dont make any purchases,  I am tinkering around with it this morning and may find something but if you get to it before me I would gladly take the help. 

even if all you do is tell me what formula to use and let me figure it out, :) helps me learn

thanks Don

Donald Ross replied to Harry Boughen on 30-Apr-13 10:40 AM
Harry this is what I got out of that.

in cell C2 =AVERAGEIFS('Fuel Tanks'!$F21:$F51,'Fuel Tanks'!$A21:$A51,">="&'Fuel Cost'!C1,'Fuel Tanks'!$A21:$A51,"<"&'Fuel Cost'!D1)

D2 =IFERROR(AVERAGEIFS('Fuel Tanks'!$F21:$F51,'Fuel Tanks'!$A21:$A51,">="&'Fuel Cost'!D1,'Fuel Tanks'!$A21:$A51,"<"&'Fuel Cost'!E1),'Fuel Cost'!C2)

E2 =IFERROR(AVERAGEIFS('Fuel Tanks'!$F21:$F51,'Fuel Tanks'!$A21:$A51,">="&'Fuel Cost'!E1,'Fuel Tanks'!$A21:$A51,"<"&'Fuel Cost'!F1),'Fuel Cost'!D2)

and so on across the page, this returns last months value if no value is found.  not the prettiest but it works.

Don


Harry Boughen replied to Donald Ross on 30-Apr-13 04:37 PM
Hi Donald,
As they say on the golf course, you don't have to draw pictures, just write down the score.
Harry
Donald Ross replied to Harry Boughen on 30-Apr-13 05:03 PM
Thank you

Don