Active Member
December 29, 2016
Dear Friend,
Please note the following query which I need to solve using a single or multiple formulae.
I have an Excel file which has the following headings:
Employee No; Employee name; Grade; department; Date of Joining - In date format; Basic pay.
I need to update a new column with the salary increase of 3% on Basic pay for A & B grade employees and 5% increase for D & E grade employees, but this increase is only for employees who joined on or before 15-Apr-2015.
Please help me with the formula stepwise.
Thank you,
Warm Regards,
Zubin
VIP
Trusted Members
June 25, 2016
VIP
April 21, 2015
Beautiful solution as always Sunny! And exactly what Zubin wanted.
However I would prefer working with variables for the grade/percentage and date. Is much 'stronger' for working in the future if there are changes coming in percentages or dates and such.
But that's more a thing of 'how to work with solid sheets' and for now the question is solved I think, but we'll hear from Zubin.
VIP
Trusted Members
June 25, 2016
Thanks Frans for your comments .
Whether to work with variables (named ranges) or otherwise will depend on the project. For bigger projects I would normally have a Setup sheet with such variables/constants defined where any changes will affect other sheets. Users will not be allowed to edit the formulas in the workbook.
For others, I would use formulas that can easily be understood and maintained by the users themselves. No array formulas, mega formulas etc unless I have no other choices.
When answering posts I have no idea what is the Excel knowledge level of the OP. I can only make a guess based on the questions asked. In such a situation, I would go for the easiest formula. It may be longer but at least the OP can edit the formulas themselves without much problem. This is just my opinion.
Wishing you and your family a Merry Christmas and a Happy 2017 New Year!!!!.
Sunny
VIP
April 21, 2015
Best wishes to you and the people who are near to you as well! And I understand your motivation and agree with it. On the other hand I doubt it if not so experienced Excel users understand this IF and OR nested formulas. We'll see!
I hope you receive some reaction from Zubin on your work. Sometimes........
VIP
Trusted Members
June 25, 2016
VIP
April 21, 2015
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Off-Topic:
Hi Frank,
I do understand your enthusiasm, but if we want to have a useful forum for our members and visitors, we have to post only on-topic messages, otherwise we will become a chat room 🙂 , and from my experience, when someone is desperately searching for a solution, it will be discouraged to continue reading our forum, he feels like we wasted his time.
I'm sure you will understand, let's do our best to create a useful and technical forum!
Happy Holidays
Catalin
VIP
April 21, 2015
Catalin Bombea said
Off-Topic:Hi Frank,
".........................I do understand your enthusiasm, but if we want to have a useful forum for our members and visitors, we have to post only on-topic messages, otherwise we will become a chat room 🙂 ................" and: "............. I'm sure you will understand, let's do our best to create a useful and technical forum!............"
Hi Catalin, you're right! I was enthousiast and think this was something adding value to the topic. But I'll look more careful in the future, cause I understand your point very well. Sunny gave two rather different approaches to the same question, both maybe not so easy for the average user, but both beautiful solutions. I bet he wouldn't have given the second one if I didn't tease him on this 🙂
But we'll try to stay on topic next times (this one being the last not on topic....).
A good 2017 also to you and your people!
Frans