LinkBack URL
About LinkBacks
Hi,
Need to calculate Best and Worst average job from the list.
An excel file attached with necessary details.
Pl. help.
Thanks,
Nagesh.
Hi,
Need to calculate Best and Worst average job from the list.
An excel file attached with necessary details.
Pl. help.
Thanks,
Nagesh.
---Deleted---
Last edited by Limor_OP; 04-10-2021 at 02:24 PM.
Please try at
F4,F7
=MAX(AVERAGEIFS(B3:B55,B3:B55,">0",A3:A55,A3:A55))
=MIN(AVERAGEIFS(B3:B55,B3:B55,">0",A3:A55,A3:A55))
E4,E7
=INDEX(A3:A55,MATCH(F4,AVERAGEIFS(B3:B55,B3:B55,">0",A3:A55,A3:A55),))
=INDEX(A3:A55,MATCH(F7,AVERAGEIFS(B3:B55,B3:B55,">0",A3:A55,A3:A55),))
Hi Bo_Ry,
Its working well. But i forgot to indicate two things.
1. The formula should exclude the Job No. begins with "HY-00" and "HY-8".
2. There will be some more exclusions with no logic, which the user only can decide. Formula to consider the exclusions which will be indicated by user as "E" in Col.C.
Grateful if you can help on this.
Thanks,
Nagesh.
If you have 'forgotten' anything else, please declare it now before anyone spends more time on your request.
The second request should be easy to do by adding another criterion to the AVERAGEIFS, e.g.
=MAX(AVERAGEIFS(B3:B55,B3:B55,">0",A3:A55,A3:A55,C3:C55,"<>E"))
Give it a try.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Add exclusion in C3:C6
Please try at
F4,F7
=AGGREGATE(14,6,AVERAGEIFS(B3:B55,B3:B55,">0",A3:A55,A3:A55,A3:A55,"<>"&IF(C3="","",C3&"*"),A3:A55,"<>"&IF(C4="","",C4&"*"),A3:A55,"<>"&IF(C5="","",C5&"*"),A3:A55,"<>"&IF(C6="","",C6&"*")),1)
=AGGREGATE(15,6,AVERAGEIFS(B3:B55,B3:B55,">0",A3:A55,A3:A55,A3:A55,"<>"&IF(C3="","",C3&"*"),A3:A55,"<>"&IF(C4="","",C4&"*"),A3:A55,"<>"&IF(C5="","",C5&"*"),A3:A55,"<>"&IF(C6="","",C6&"*")),1)
E4,E7
=INDEX(A3:A55,MATCH(F4,AVERAGEIFS(B3:B55,B3:B55,">0",A3:A55,A3:A55,A3:A55,"<>"&IF(C3="","",C3&"*"),A3:A55,"<>"&IF(C4="","",C4&"*"),A3:A55,"<>"&IF(C5="","",C5&"*"),A3:A55,"<>"&IF(C6="","",C6&"*")),))
=INDEX(A3:A55,MATCH(F7,AVERAGEIFS(B3:B55,B3:B55,">0",A3:A55,A3:A55,A3:A55,"<>"&IF(C3="","",C3&"*"),A3:A55,"<>"&IF(C4="","",C4&"*"),A3:A55,"<>"&IF(C5="","",C5&"*"),A3:A55,"<>"&IF(C6="","",C6&"*")),))
I am sorry for that Ms. Ali. Will take care in future.
Your solution is working for my second request.
Grateful if i get solution for my first request too.
Thanks,
Nagesh.
Almost ok but I think i could not covney what i wanted with regard to C5 formula. The exclusion should be applicable to the entire column (C3:C55). Ex: Worst Avg. is 820 Kg. but i want it to be excluded (I will put "E" in col. C of respective cell) so that the formula should consider next worst avg. i.e. 4860 Kg. (HY-06192).
HY-00 & HY-8 can be inside the formula, since the above formula covers C3:C55.
Hope i could convey what i wanted.
Pl. help.
Nagesh.
Administrative Note:
We don't expect you to bump threads sooner than 24 hours after your last post or more than once a day - it has been only an hour and 10 minutes since you last posted. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.
HY-08 and HY-00 are put in cells because AVERAGEIFS does not allow the use of functions such as LEFT which would typically be used.
Cut/Paste table in C to Column D
Formula in G4 of Attached
=AGGREGATE(14,6,AVERAGEIFS(B3:B55,B3:B55,">0",C3:C55,"<>E",A3:A55,A3:A55,A3:A55,"<>"&IF(D3="","",D3&"*"),A3:A55,"<>"&IF(D4="","",D4&"*"),A3:A55,"<>"&IF(D5="","",D5&"*"),A3:A55,"<>"&IF(D6="","",D6&"*")),1)
in G7
=AGGREGATE(15,6,AVERAGEIFS(B3:B55,B3:B55,">0",C3:C55,"<>E",A3:A55,A3:A55,A3:A55,"<>"&IF(D3="","",D3&"*"),A3:A55,"<>"&IF(D4="","",D4&"*"),A3:A55,"<>"&IF(D5="","",D5&"*"),A3:A55,"<>"&IF(D6="","",D6&"*")),1)
Hi John Topley,
Your formula working fine at G4 & G7, but shows #N/A in F4. Ex: HY-05840 appears in 3 places and and when i put "E" in one of the cells in Col. C, the answer is working fine in G4 but F4 shows #N/A, but it should show HY-05840.
Thanks,
Nagesh.
Ms. Ali, My apologies for that.
I works fine for me as you will see from the file I posted: did you enter the formulae with Ctrl+Shift+Enter?
I incorporated C3:C55,"<>E" in F4 and now its working perfectly.
Thank you so much. You made my day.
Thank you once again John Topley, Bo_Ry, Ali for your time and effort.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks