Excel formula in the calculation of month-wise annual basic pay

[baidya91]

Suppose, an employee has a basic pay of Rs. 72600/- in the month of March, 2021.
He will get his annual increment in the month of July, 2021.
He will get a promotion increment in the month of November, 2021 on 30-10-2021 in the following way.:-

MONTH	BASIC PAY	Remark
Mar-21​	72600​
Apr-21​	72600​
May-21​	72600​
Jun-21​	72600​
Jul-21​	74800​
Aug-21​	74800​
Sep-21​	74800​
Oct-21​	74800​
Nov-21​	74873​	As he will receive promotional benefit w.e.f. 30-11-21, he will enjoy only 1 day's benefit in that month
Dec-21​	77000​
Jan-22​	77000​
Feb-22​	77000​

Now which single excel formula shall I use to prepare his month-wise annual basic pay with or without all the increments.
I want to mean a single excel formula (may be nested) that applies to all if they get one or two increments as mentioned above or not a single one of the increments.
Please help. Thanks in advance.

 

[Dave Patton]

You will have to edit the formula if you use multiple different factors.
Try either of the Lookup formulas. Column A has real dates.

T202105a.xlsm
	A	B	C	D	E	F
1		72,600
2	MONTH	BASIC PAY
3	1-Mar-21	72,600	72,600		1	1.06060606
4	1-Apr-21	72,600	72,600		3	1.00000000
5	1-May-21	72,600	72,600		7	1.03030303
6	1-Jun-21	72,600	72,600		11	1.03130854
7	1-Jul-21	74,800	74,800		12	1.06060606
8	1-Aug-21	74,800	74,800
9	1-Sep-21	74,800	74,800
10	1-Oct-21	74,800	74,800
11	1-Nov-21	74,873	74,873
12	1-Dec-21	77,000	77,000
13	1-Jan-22	77,000	77,000
14	1-Feb-22	77,000	77,000
15
3a

Cell Formulas
Range	Formula
B3:B14	B3	=LOOKUP(MONTH(A3),{1,1.06060606060606;3,1;7,1.03030303030303;11,1.0313085399449;12,1.06060606060606})*$B$1
C3:C14	C3	=LOOKUP(MONTH(A3),$E$3:$F$7)*$B$1
A4:A14	A4	=EDATE(A3,1)

 

[Dave Patton]

Try using Choose

T202105a.xlsm
	A	B	C	D	E	F
1		72,600	Version	2
2	MONTH	BASIC PAY
3	1-Mar-21	72,600			1	1.06060606
4	1-Apr-21	72,600			3	1.00000000
5	1-May-21	72,600			7	1.03030303
6	1-Jun-21	72,600			11	1.03130854
7	1-Jul-21	74,800			12	1.06060606
8	1-Aug-21	74,800
9	1-Sep-21	74,800			Version 1	0.00000000
10	1-Oct-21	74,800			Version 2
11	1-Nov-21	74,873
12	1-Dec-21	77,000
13	1-Jan-22	77,000
14	1-Feb-22	77,000
3a

Cell Formulas
Range	Formula
B3:B14	B3	=CHOOSE(MATCH($D$1,{1,2},0),$B$1*1,LOOKUP(MONTH(A3),$E$3:$F$7)*$B$1)
A4:A14	A4	=EDATE(A3,1)

 

[baidya91]

Try using Choose

T202105a.xlsm
	A	B	C	D	E	F
1		72,600	Version	2
2	MONTH	BASIC PAY
3	1-Mar-21	72,600			1	1.06060606
4	1-Apr-21	72,600			3	1.00000000
5	1-May-21	72,600			7	1.03030303
6	1-Jun-21	72,600			11	1.03130854
7	1-Jul-21	74,800			12	1.06060606
8	1-Aug-21	74,800
9	1-Sep-21	74,800			Version 1	0.00000000
10	1-Oct-21	74,800			Version 2
11	1-Nov-21	74,873
12	1-Dec-21	77,000
13	1-Jan-22	77,000
14	1-Feb-22	77,000
3a

Cell Formulas
Range	Formula
B3:B14	B3	=CHOOSE(MATCH($D$1,{1,2},0),$B$1*1,LOOKUP(MONTH(A3),$E$3:$F$7)*$B$1)
A4:A14	A4	=EDATE(A3,1)

Thanks. It works wonderfully.

 

[baidya91]

Now what will be formulas if following conditions are set:-

Condition 1:-
Basic Pay in March, 2021		72600
If annual increment received in the month of July, 2021		Yes
After Annual Increment, Basic Pay in the month of July, 2021		74800
If promotional increment received in the month of July, 2021		Yes
After Promotional Increment, Basic Pay in the month of July, 2021		77000
Condition 2:-
Basic Pay in March, 2021		72600
If annual increment received in the month of July, 2021		No
AfterNo Annual Increment, Basic Pay in the month of July, 2021		73600
If promotional increment received in the month of July, 2021		No
After No Promotional Increment, Basic Pay in the month of July, 2021		72600
Condition 3:-
Basic Pay in March, 2021		72600
If annual increment received in the month of July, 2021		Yes
AfterNo Annual Increment, Basic Pay in the month of July, 2021		73600
If Promotional increment received but on a certain date		Yes
After Promotional Increment, Basic Pay on that certain date		at the rate of 77000
The date may be variable like		30/08/2021 or 08/02/2021 etc
How to combine these three conditions in the given excel formula?

 

[baidya91]

Sir, I have edited the formula as I am using multiple different factors. But promotional increment is bothering me as its date is variable. How to revise the following edited formula if the promotional increment date is variable from 1st July to 31st December? Please Help:-

=IF(AND($H$3="Yes",$H$5="Yes"),LOOKUP(MONTH(A3),{1,1.06060606060606;3,1;7,1.03030303030303;11,1.0313085399449;12,1.06060606060606})*$B$1,IF(AND($H$3="No",$H$5="No"),$B$1,IF(AND($H$3="Yes",$H$5="No"),LOOKUP(MONTH(A3),{1,1.06060606060606;3,1;7,1.03030303030303;11,1.0313085399449;12,1.06060606060606})*$B$1)))

 

[baidya91]

Sir, may I not get further help?

 

[Dave Patton]

How many different results (Options) do you require?
What is the arithmetic for each option?
Did you try using the Choose method that I suggested?

If you have many different and variable criteria, you may want to build a User Defined Function UDF.

T202105a.xlsm
	A	B	C	D
1		72,600	Option required	Option 2
2	MONTH	BASIC PAY
3	1-Mar-21	72,600
4	1-Apr-21	72,600
5	1-May-21	72,600
6	1-Jun-21	72,600
7	1-Jul-21	74,800
8	1-Aug-21	74,800
9	1-Sep-21	74,800
10	1-Oct-21	74,800
11	1-Nov-21	74,873
12	1-Dec-21	77,000
13	1-Jan-22	77,000
14	1-Feb-22	77,000
3a

Cell Formulas
Range	Formula
B3:B14	B3	=CHOOSE(MATCH($D$1,{"Option 1","Option 2"},0),$B$1*1,LOOKUP(MONTH(A3),rOption2)*$B$1)
A4:A14	A4	=EDATE(A3,1)

Named Ranges
Name	Refers To	Cells
rOption2	='3a'!$E$3:$F$7	B3:B14

 

[baidya91]

I have tried the CHOOSE method. That particular option you have mentioned works fine. But I have the following options:-

	72600​	72600​	72600​	72600​
MONTH	BASIC1	BASIC2	BASIC3	BASIC3
Mar-21​	72600​	72600​	72600​	72600​
Apr-21​	72600​	72600​	72600​	72600​
May-21​	72600​	72600​	72600​	72600​
Jun-21​	72600​	72600​	72600​	72600​
Jul-21​	74800​	72600​	74800​	77000​
Aug-21​	74800​	72600​	74800​	77000​
Sep-21​	74800​	72600​	74800​	77000​
Oct-21​	74800​	72600​	74800​	77000​
Nov-21​	74873​	72600​	74800​	77000​
Dec-21​	77000​	72600​	74800​	77000​
Jan-22​	77000​	72600​	74800​	77000​
Feb-22​	77000​	72600​	74800​	77000​

But I cannot find out what will be $E$3:$F$7 in each case. Moreover, if the promotional increment date (as in the first case) changes, what will be $E$3:$F$7? Maybe, I am bothering you. But I cannot help doing it, Sir. Excuse me if I am wrong.

 

[Dave Patton]

How many different results (Options) do you require?
What is the arithmetic for each option?
Did you try using the Choose method that I suggested?

If you have many different and variable criteria, you may want to build a User Defined Function UDF.
I changed the example to agree to the last names and order in your post. The lookup information was shown in previous posts.
Try experimenting with Lookup so that you understand how that formula works.
Work out the arithmetic for the options that you require.
Add additional Lookup ranges for your options. It is not necessary to name the ranges, but the formula will be easier to read if you name the ranges.
Add the additional options to the formula.
N.B. You can paste the post to a clean sheet. See the icon below the f(x)
You can post an extract with the forum's tool XL2BB.

T202105a.xlsm
	A	B	C	D	E	F
1		72,600	Option required	Basic1
2	MONTH	Pay
3	1-Mar-21	72,600		Basic1	1	1.06060606
4	1-Apr-21	72,600			3	1.00000000
5	1-May-21	72,600			7	1.03030303
6	1-Jun-21	72,600			11	1.03130854
7	1-Jul-21	74,800			12	1.06060606
8	1-Aug-21	74,800
9	1-Sep-21	74,800		Basic2		1.0
10	1-Oct-21	74,800
11	1-Nov-21	74,873
12	1-Dec-21	77,000
13	1-Jan-22	77,000
14	1-Feb-22	77,000
3a

Cell Formulas
Range	Formula
B3:B14	B3	=CHOOSE(MATCH($D$1,{"Basic1","Basic2"},0),LOOKUP(MONTH(A3),rBasic1)*$B$1,$B$1*1)

Named Ranges
Name	Refers To	Cells
'3a'!rBasic1	='3a'!$E$3:$F$7	B3:B14

T202105a.xlsm
	A	B	C	D	E	F
1		72,600	Option required	Basic2
2	MONTH	Pay
3	1-Mar-21	72,600		Basic1	1	1.06060606
4	1-Apr-21	72,600			3	1.00000000
5	1-May-21	72,600			7	1.03030303
6	1-Jun-21	72,600			11	1.03130854
7	1-Jul-21	72,600			12	1.06060606
8	1-Aug-21	72,600
9	1-Sep-21	72,600		Basic2		1.0
10	1-Oct-21	72,600
11	1-Nov-21	72,600
12	1-Dec-21	72,600
13	1-Jan-22	72,600
14	1-Feb-22	72,600
3a

Cell Formulas
Range	Formula
B3:B14	B3	=CHOOSE(MATCH($D$1,{"Basic1","Basic2"},0),LOOKUP(MONTH(A3),rBasic1)*$B$1,$B$1*1)

Named Ranges
Name	Refers To	Cells
'3a'!rBasic1	='3a'!$E$3:$F$7	B3:B14