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:
Please Login or Register to view this content.
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.
Does the formula I posted not work? For me, it returns Concrete: 25.55555, Hardcore: 20, and Tarmac: 26.25...
Hi,
F17 copied across
Formula:Please Login or Register to view this content.
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.
...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.
Richard sorry I have used yoru formula I reposted before I checked and was too busy celebrating to post. The formula is perfect, I have fed into my larger spreadsheet and it works a treat. The only issue I am having is When there is no data it is returning a DIV/0 error. This messes up other formula that I have going. Is there away to get it to return just a blank cell instead of DIV/0 error??
That is a very good point, the lorries we use are standard and I have never seen a different truck. I will ask my manager whether we use different sized trucks and get back to you. Thansk for flagging that.
I've solved the IFFError thing, Richard you are a genius. Many thanks!
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:Please Login or Register to view this content.
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:Please Login or Register to view this content.
Last edited by Richard Buttrey; 02-26-2016 at 11:17 AM.
...
And another method if you were to add a helper column Q to sheet 2, i.e.
Q6
Formula:Please Login or Register to view this content.
Then on sheet1 in F17
Formula:Please Login or Register to view this content.
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:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks