Hi,
I have a file where in F column the data have mentioned the year of working. I need the data mentioned in K and L column. K column result based the data mentioned from F column and L column data will take from G column. File attached for your reference. Thanks.
Hi there,
I have added a couple of 'helper columns' to breakdown the months and years.
From there we can use the 'Countifs' and 'Averageifs' formulas.
Hope this helps.
Last edited by ORoos; 10-25-2022 at 05:43 AM. Reason: Corrected Average from col H to G
If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.
Since I assume employees can't fall into two categories, I've assume that the month ranges are m<6, 6<=m<12, 12<=m<24, 24<=m. Then:
See attached.
Please Login or Register to view this content.
WBD
Office 365 on Windows 11, looking for ✶ rep!
Another
in K2
Formula:
Please Login or Register to view this content.
in K3
Formula:Please Login or Register to view this content.
in L2
Formula:Please Login or Register to view this content.
similar for others
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Hi WBD,
Good formula. Just one point: there are variations of Year vs Years and Month vs Months. Does your formula account for this?
An alternative, using FILTERXML. Results are different from WBD
I used <6mo, >-6mo and <12mo,>=1Yr and <2Yr, >=2Yr
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Good catch! Changing "Years" to "Year" should do itOriginally Posted by ORoos
WBDPlease Login or Register to view this content.
Dear All,
Thanks for your effort to solve my issue. Actual i had attached the sheet where i have highlighted the column in yellow and attached sheet again.
I need a formula accordingly.
one way with Power Query (with remarks defined by you see v2)
remarks Count Average Above 2 years 1 year to 2 year 6 months to 1 year 0 to 6 months
and defined by me
Data Count Average Paid 0-6 Months 1 year to 2 year 6 months to 1 year Above 2 years
Last edited by sandy666; 10-25-2022 at 06:25 AM.
Where do you want a formula? Column I or column L... or both????
You are also being inconsistent. 6 months counts as 6mo to 1 year. BUT 1 year does NOT count as 1 year to 2 year.
The usual question: is 0-6 months .......is it 0 to <= 6 months or 0 to < 6 months (and same for other comparisons).
Row 6 and Row 17 in your example are contradictory. Column F says "6 Months" for both these rows but Column I has different values. You're asking the impossible.
WBD
Last edited by WideBoyDixon; 10-25-2022 at 08:15 AM.
Dear Mr. Glenn,
I need the formula to be set in L column based on the value of column F. In I column i have just mentioned the category which it will follow.
Dear john,
i need the value to be considered of 6 months from 0 to 6 months and in 6 months to 1 year it will not consider. And where the value 1 year mentioned it will be considered under 6 months to 1 year term and not above. In L column i have mentioned the value manually for which i need a formula.
so which - if any - of the responses is correct?
Try this. It looks (to me) like 4 of your expected answers are incorrect. They are highlighted in cols A to D.
Last edited by Glenn Kennedy; 10-25-2022 at 12:41 PM.
If that's correct then you can just change the comparison operators in these:
WBDPlease 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