Hi,
I am trying to automate a tedious excel process, I want to be able to Average rates dependant on a number of criterias but instead of criteria 1 and criteria 2 ... I want it to average if teh data is criteria 1 OR criteria 2 etc.
I have attached the excel spreadsheet I am working from.
Many thanks,
Will
Are you just looking for the average rate for the e.g. Tarmac entries on sheet 2? If so, this should work - enter in H17 on sheet1 and fill left:
Formula:
=AVERAGEIFS(Sheet2!$K:$K,Sheet2!$F:$F,H$8)
If not, can you clarify the end result of what you're looking for? What value should you get in F17:H17?
I am after the average of all the rates for that week for each different material. So in F17 I should get 25.55555, G17 26.25 and H17 20. Once I get the average rate for each material for the whole week I can multiply it by the total volume and work out what I have spent.
Hope thats clear,
Cheers.
Hi,
F17 copied across
Formula:=AVERAGEIFS(Sheet2!$K:$K,Sheet2!$D:$D,">="&$D9,Sheet2!$D:$D,"<="&$D15,Sheet2!$F:$F,Sheet1!F$8)
I think there's a typo in your example. You have G17 & H17 swapped over.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Does the formula I posted not work? For me, it returns Concrete: 25.55555, Hardcore: 20, and Tarmac: 26.25...
...I'm assuming the sheet 2 is a special case and in reality there will be many dates which straddle many weeks/months. Maybe Vokey9 will comment.Originally Posted by cantosh
True.
Vokey9, Richard's formula performs and additional date check that mine doesn't. If the date ranges on sheets one and two aren't going to match all the time, then definitely use his formula. If the dates will always match, then either formula should give you the same results.
Hi Yes richard is right I have included an updated version which I think makes it clearer. There are multiple dates with multiple material delivered.
See attached.
@Vokey9
I believe it would be more appropriate to post the value that must obtain in F17 instead of a non-working formula...
Hi,
Why did you not use the formula I gave you? You're still not building in the date criteria and appear to have ignored my suggestion.
However I think there's a more fundamental potential error. Are you sure you want a simple average of the rates?
In this trivial example it won't be a problem since all the volumes are exactly the same whereas in reality they will almost certainly vary. In which case and statistically speaking a simple average of rates would be incorrect. You should be using a weighted average by building in the volume.
I have just found out that every now and then we use a 9m3 lorry. How would I go about using a weighted average to make the tool comprehensive?
Hi,
This would therefore be a better formula in F17. Adjust the 1000 row range as necessary to cover the maximum range - or create dynamic range names that automatically adjust.
Formula:=IFERROR(SUMPRODUCT((Sheet2!$K$6:$K$1000)*(Sheet2!$M$6:$M$1000)*(Sheet2!$F$6:$F$1000=F$8))/SUMIFS(Sheet2!$M$6:$M$1000,Sheet2!$F$6:$F$1000,F$8),0)
Just for my own peace of mind cabn you just explain to me why I dont need to reference the dates I want to extract data for?? I cant figure out how it works!!
I have just put the forumula in to my spreadsheet and realised that unfortunately I am getting an average rate for all dates, I want to only get an average for the dates in teh table in sheet 1. I feel like I need a SUMPRODUCTSIF function but that doesnt exist!
Is there anyway of combining the first and second equation that Richard gave me??
Hi,
Sorry, forgot to add the date criteria to the SUMPRODUCT and SUMIFS parts
Formula:=IFERROR(SUMPRODUCT((Sheet2!$K$6:$K$1000)*(Sheet2!$M$6:$M$1000)*(Sheet2!$F$6:$F$1000=F$8)*(Sheet2!$D$6:$D$1000>=$D9)*(Sheet2!$D$6:$D$1000<=$D15))/SUMIFS(Sheet2!$M$6:$M$1000,Sheet2!$F$6:$F$1000,F$8,Sheet2!$D$6:$D$1000,">="&$D9,Sheet2!$D$6:$D$1000,"<="&$D15),0)
Last edited by Richard Buttrey; 02-26-2016 at 11:17 AM.
Does the last equation incorperate the dates?? If so I will try using that,for some reason I cant translate across teh original forumal to my master copy I keep getting massive numbers as a rate!
My head is fried I cant begin to work out how to incorperate the dates, I realise it will be similar to above but i dont fully understand it!?
Hi,
Sorry the date criteria were missing from the formula in post #19.
The general layout of SUMIFS() functions are
SUMIFS(RangeToSum,1stCriteriaRange,1stCriteria,2ndCriteriaRange,2ndCriteria......nthCriteriaRange,nthCriteria
Hence you'll need two new pairs of criteria, one each for the start and end dates of the week in each of the SUMIFS
Formula:=IFERROR(SUMIFS(Sheet2!$Q$6:$Q$12,Sheet2!$F$6:$F$12,F$8,Sheet2!$D$6:$D$1000,">="&$D9,Sheet2!$D$6:$D$1000,"<="&$D15)/SUMIFS(Sheet2!$M$6:$M$12,Sheet2!$F$6:$F$12,F$8,Sheet2!$D$6:$D$1000,">="&$D9,Sheet2!$D$6:$D$1000,"<="&$D15)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks