If statement

ChetanPuri · Apr 29, 2023

Hi All Excel Gurus, I need some help in building a formula for salaries Budget. In our organisation there are 8 Levels and each Level has 3 steps which changes at each anniversary until the employee reaches his maximum step H and stays there until he is promoted to next level. For example employee 1 is on Level 3 and he is on step L with $60000, at his next anniversary which is 05 September 2023 he will move onto the next step M and will have a salary of 65,000. I would like to have a formula where on 05 September excel automate this process and changes his salary step to M and matches and calculate his salary of 65k and then calculate the balance to end of financial year 30 June 2024, by subtracting the remaining number of days between 30 June 2024 - 05 September 2023. Any help will be appreciated. Many thanks, Regards Chetan

awoohaw · Apr 29, 2023

Hi Chetan, it would be SUPER helpful to post your scenario in a mini worksheet using xl2bb add in (link below). At worse, as a table. You can sanitize your data before posting. While pictures are helpful, they are not copy/paste-able.

The forum wants to help, but creating a scenario manually is both time consuming and subject errors. Help the forum help you. I can assure you that posting unambiguous data / scenario you will get multiple replies in minutes.

Thanks in advance.

awoohaw · Apr 29, 2023

So, I tried to decipher your description. If this isn't remotely close to what you want, please clarify:

Book1

A

B

C

D

E

F

G

H

I

J

K

4

Employee

hire date

Current Level

Current Step

Next Level

Next Step

Next Salary

Next Anniversary

End of Next Fiscal Year

Salary Until Next Fiscal Year

5

Employee1

2020-09-05

3

L

2024-06-30

6

7

8

9

Level

Step

Salary

10

1

L

11

1

M

12

1

H

13

2

L

14

2

M

15

2

H

16

3

L

60000

17

3

M

65000

18

3

H

19

4

L

20

4

M

21

4

H

22

5

L

23

5

M

24

5

H

25

6

L

26

6

M

27

6

H

28

7

L

29

7

M

30

7

H

31

8

L

32

8

M

33

8

H

34

Sheet1

Cell Formulas
Range	Formula
E5	E5	=IF(D5="H",C5,INDEX(A10:A33,MATCH(C5&D5,A10:A33&B10:B33,0)+1))
F5	F5	=IF(D5="H",C5,INDEX(B10:B33,MATCH(C5&D5,A10:A33&B10:B33,0)+1))
G5	G5	=IF(D5="H",C5,INDEX(C10:C33,MATCH(C5&D5,A10:A33&B10:B33,0)+1))
H5	H5	=DATE(IF(DATE(YEAR(I5),MONTH(B5),DAY(B5))>I5,YEAR(I5)-1,I5),MONTH(B5),DAY(B5))
J5	J5	=((I5-H5)/365)*G5

awoohaw · Apr 29, 2023

I had an error in one calculation in the worksheet above, It doesn't change the above scenario but other scenarios will get errors.
Here it is again with the correction:

Book1

A

B

C

D

E

F

G

H

I

J

K

L

3

4

Employee

hire date

Current Level

Current Step

Next Level

Next Step

Next Salary

Next Anniversary

End of Next Fiscal Year

Salary Until Next Fiscal Year

5

Employee1

2020-09-05

3

L

2024-06-30

6

7

8

9

Level

Step

Salary

10

1

L

11

1

M

12

1

H

13

2

L

14

2

M

15

2

H

16

3

L

60000

17

3

M

65000

18

3

H

19

4

L

20

4

M

21

4

H

22

5

L

23

5

M

24

5

H

25

6

L

26

6

M

27

6

H

28

7

L

29

7

M

30

7

H

31

8

L

32

8

M

33

8

H

Sheet1

Cell Formulas
Range	Formula
E5	E5	=IF(D5="H",C5,INDEX(A10:A33,MATCH(C5&D5,A10:A33&B10:B33,0)+1))
F5	F5	=IF(D5="H",C5,INDEX(B10:B33,MATCH(C5&D5,A10:A33&B10:B33,0)+1))
G5	G5	=IF(D5="H",C5,INDEX(C10:C33,MATCH(C5&D5,A10:A33&B10:B33,0)+1))
H5	H5	=DATE(IF(DATE(YEAR(I5),MONTH(B5),DAY(B5))>I5,YEAR(I5)-1,YEAR(I5)),MONTH(B5),DAY(B5))
J5	J5	=((I5-H5)/365)*G5

ChetanPuri · Apr 29, 2023

awoohaw said:
I had an error in one calculation in the worksheet above, It doesn't change the above scenario but other scenarios will get errors.
Here it is again with the correction:

Book1
A B C D E F G H I J K L
3
4 Employee hire date Current Level Current Step Next Level Next Step Next Salary Next Anniversary End of Next Fiscal Year Salary Until Next Fiscal Year
5 Employee1 2020-09-05 3 L 3 M 65000 2023-09-05 2024-06-30 53246.57534
6
7
8
9 Level Step Salary
10 1 L
11 1 M
12 1 H
13 2 L
14 2 M
15 2 H
16 3 L 60000
17 3 M 65000
18 3 H
19 4 L
20 4 M
21 4 H
22 5 L
23 5 M
24 5 H
25 6 L
26 6 M
27 6 H
28 7 L
29 7 M
30 7 H
31 8 L
32 8 M
33 8 H
Sheet1
Cell Formulas
Range Formula
E5 E5 =IF(D5="H",C5,INDEX(A10:A33,MATCH(C5&D5,A10:A33&B10:B33,0)+1))
F5 F5 =IF(D5="H",C5,INDEX(B10:B33,MATCH(C5&D5,A10:A33&B10:B33,0)+1))
G5 G5 =IF(D5="H",C5,INDEX(C10:C33,MATCH(C5&D5,A10:A33&B10:B33,0)+1))
H5 H5 =DATE(IF(DATE(YEAR(I5),MONTH(B5),DAY(B5))>I5,YEAR(I5)-1,YEAR(I5)),MONTH(B5),DAY(B5))
J5 J5 =((I5-H5)/365)*G5

ChetanPuri · Apr 29, 2023

Hi Awoohaw,

My apologies, I tried to use XL2bb but somehow not able to install an add in. Tried many times so including a snip it. I tried the formula you mentioned but getting an error. The highlighted columns in yellow are the ones where I have used the formula and snip it includes the other date . Many thanks for you your help.
Regards,
Chetan

ChetanPuri · Apr 29, 2023

ChetanPuri said:
Book1
A B C D E F G H I J K L M N O P Q R S T
1 Employee Code Surname Given Position Number Position Title Start Date (with org) Contract Expiry Date 30/06/2024 Employee Type Hours/Fortnight (as at EOM, inc flexible arrangements etc) Classification Step Division Description Next Level Next Step Next Salary Level Level Step Salaries
2 348 5-Sep-22 5-Sep-23 Permanent 76.00 L7 1 Strategy, Planning and Performance 2 117,152.00 L1 59,097.00
3 305 5-Jan-22 #NUM! Permanent 76.00 L6 1 Regional Health Integration 2 105,290.00 L2 62,516.00
4 319 23-May-22 #NUM! Permanent 76.00 L6 3 Corporate Services 1 111,221.00 L3 1 66,732.00
5 355 12-Sep-22 12-Sep-23 Permanent 76.00 L7 1 Regional Health Integration 2 117,152.00 L3 2 72,665.00
6 375 13-Mar-23 30-Jun-23 #NUM! Maximum Term 76.00 L6 3 Strategy, Planning and Performance 1 111,221.00 L3 3 75,275.00
7 100 4-Sep-17 4-Sep-23 Permanent 60.80 L3 3 Office of the CEO 1 77,112.00 L4 1 77,112.00
8 131 7-Feb-18 #NUM! Permanent 76.00 L4 2 Strategy, Planning and Performance 3 85,655.00 L4 2 83,045.00
9 221 19-Sep-19 19-Sep-23 Permanent 76.00 L6 2 Corporate Services 3 109,305.00 L4 3 85,655.00
10 195 4-Feb-19 Permanent 76.00 L6 1 Regional Health Integration 2 105,290.00 L5 1 87,493.00
11 366 5-Dec-22 Secondment 76.00 L7 2 Strategy, Planning and Performance 3 121,830.00 L5 2 93,426.00
12 298 29-Nov-21 Permanent 76.00 L5 1 Regional Health Integration 2 93,426.00 L5 3 96,036.00
13 183 12-Nov-18 30-Jun-23 Executive Contract 76.00 s Senior Executive Leadership Team #N/A L6 1 99,357.00
14 106 30-Oct-17 Permanent 76.00 L7 3 Regional Health Integration 1 125,793.00 L6 2 105,290.00
15 345 1-Sep-22 Permanent 76.00 L6 1 Corporate Services 2 105,290.00 L6 3 109,305.00
16 357 19-Sep-22 Permanent 76.00 L7 1 Strategy, Planning and Performance 2 117,152.00 L7 1 111,221.00
17 326 7-Jul-22 Permanent 76.00 L3 1 Office of the CEO 2 72,665.00 L7 2 117,152.00
18 11 6-Sep-21 Permanent 76.00 L6 3 Corporate Services 1 111,221.00 L7 3 121,830.00
19 325 6-Jul-22 Permanent 76.00 L7 1 Regional Health Integration 2 117,152.00 L8 1 125,793.00
20 88 11-Jul-17 30-Jun-23 Executive Contract 76.00 s Senior Executive Leadership Team #N/A L8 2 130,360.00
21 312 14-Feb-22 Permanent 76.00 L5 1 Corporate Services 2 93,426.00 L8 3 134,927.00
22 290 23-Aug-21 Permanent 76.00 L5 1 Corporate Services 2 93,426.00 L8 4 139,495.00
23 376 27-Mar-23 27-Mar-26 Executive Contract 76.00 s Senior Executive Leadership Team #N/A
24 231 21-Oct-19 Permanent 76.00 L4 1 Corporate Services 2 83,045.00
25 303 4-Jan-22 Secondment 76.00 L6 1 Health Workforce 2 105,290.00
26 134 7-Mar-18 Permanent 60.80 L8 2 Corporate Services 3 134,927.00
27 369 5-Jan-23 24-Nov-23 Maximum Term 76.00 L4 2 Corporate Services 3 85,655.00
Raw Data to flow in worksheets
Cell Formulas
Range Formula
R5:R6,R20:R22,R17:R18,R14:R15,R11:R12,R8:R9 R5 =R4
H2:H9 H2 =DATE(IF(DATE(YEAR($H$1),MONTH(F2),DAY(F2))>$H$1,YEAR($H$1)-1,$H$1),MONTH(F2),DAY(F2))
P2:P12,P24:P27,P21:P22,P14:P19 P2 =IF(L2="1",K2,INDEX($T$2:$T$22,MATCH(K2&L2,$R$2:$R$22&$S$2:$S$22,0)+1))
O2:O27 O2 =IF(L2="1",K2,INDEX($S$2:$S$22,MATCH(K2&L2,$R$2:$R$22&$S$2:$S$22,0)+1))

The issue I have in this on row 87 Column O when it is the last step in the Level 8, the formula doesn't recognises it and secondly when someone on the Last level for example row 71 column o the salary jumps on to the next level 4 step 1.

awoohaw · Apr 30, 2023

@ChetanPuri , Thanks for posting the xl2bb in post 7. But, I'm not sure why you posted an image as images do absolutely nothing in providing information to debug formulas that are not working.

But, I've tried to compare your Post 7 to my Post 4. and I can't align your data elements with what I did. Can you look at this caparison of column headers and align the column on the left to the column on the right?

Thanks.

If Question Anniverary Remainder of Year Salary Question.xlsm

A

B

C

1

What was given in Post#4

Was you gave in Post #7

2

Employee

Employee Code

3

hire date

Surname

4

Current Level

Given

5

Current Step

Position Number

6

Next Level

Position Title

7

Next Step

Start Date (with org)

8

Next Salary

Contract Expiry Date

9

Next Anniversary

2024-06-30

10

End of Next Fiscal Year

Employee Type

11

Salary Until Next Fiscal Year

Hours/Fortnight (as at EOM, inc flexible arrangements etc)

12

Classification

13

Step

14

Division Description

15

Next Level

16

Next Step

17

Next Salary Level

18

(blank column)

19

Level

20

Step

21

Salaries

Sheet3

ChetanPuri · Apr 30, 2023

awoohaw said:
@ChetanPuri , Thanks for posting the xl2bb in post 7. But, I'm not sure why you posted an image as images do absolutely nothing in providing information to debug formulas that are not working.

But, I've tried to compare your Post 7 to my Post 4. and I can't align your data elements with what I did. Can you look at this caparison of column headers and align the column on the left to the column on the right?

Thanks.

If Question Anniverary Remainder of Year Salary Question.xlsm
A B C
1 What was given in Post#4 Was you gave in Post #7
2 Employee Employee Code
3 hire date Surname
4 Current Level Given
5 Current Step Position Number
6 Next Level Position Title
7 Next Step Start Date (with org)
8 Next Salary Contract Expiry Date
9 Next Anniversary 2024-06-30
10 End of Next Fiscal Year Employee Type
11 Salary Until Next Fiscal Year Hours/Fortnight (as at EOM, inc flexible arrangements etc)
12 Classification
13 Step
14 Division Description
15 Next Level
16 Next Step
17 Next Salary Level
18 (blank column)
19 Level
20 Step
21 Salaries
Sheet3

Sorry, please see below, somehow I was able to make XLBB work. Also, I was able to make the create the worksheet and will share the source sheet

Employee positions report_31 March 2023-30 April 2023.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

1

Employee Code

Surname

Given

Position Number

Position Title

Start Date (with org)

Contract Expiry Date

Anniversary Date

Employee Type

Hours/Fortnight (as at EOM, inc flexible arrangements etc)

Classification

Step

Current Salary

Division Description

Next Step

Next Salary Level - Incudes P-T Calculation

As Per New EBA

July to Anniversary Date

Anniversry date to NEW EBA

EBA to End of Financial Year

Total Salary

SUPER

A/L Loading

Work Cover

Salary with Oncost

Level

Step

Salaries

2

348

309

Strategic and Planning Lead (Alcohol and Other Drugs)

5-Sep-22

5-Sep-23

Permanent

76.00

L7

2

117,152.00

Strategy, Planning and Performance

L1

59,097.00

3

305

57

Primary Health Development Coordinator

5-Jan-22

5-Jan-24

Permanent

76.00

L6

1

99,357.00

Regional Health Integration

11,572.47

2,104.08

L2

62,516.00

4

319

208

Marketing and Communications Advisor

23-May-22

23-May-24

Permanent

76.00

L6

3

Corporate Services

16,939.70

3,079.95

L3

1

66,732.00

5

355

36

Regional Health Partnerships Lead

12-Sep-22

12-Sep-23

Permanent

76.00

L7

1

111,221.00

Regional Health Integration

12,978.34

2,359.70

2

72,665.00

6

375

308

Strategic Planning Coordinator

13-Mar-23

30-Jun-23

13-Mar-24

Maximum Term

76.00

L6

3

109,305.00

Strategy, Planning and Performance

14,600.87

2,654.70

3

75,275.00

7

100

72

Administration Support Officer

4-Sep-17

4-Sep-23

Permanent

60.80

L3

3

Office of the CEO

6,747.83

1,226.88

L4

1

77,112.00

8

131

30

Executive Coordinator

7-Feb-18

7-Feb-24

Permanent

76.00

L4

2

83,045.00

Strategy, Planning and Performance

10,365.19

1,884.58

2

83,045.00

9

221

295

Procurement Business Partner

19-Sep-19

19-Sep-23

Permanent

76.00

L6

2

Corporate Services

12,133.51

2,206.09

3

85,655.00

10

195

37

Regional Health Partnerships Coordinator

4-Feb-19

4-Feb-24

Permanent

76.00

L6

1

99,357.00

Regional Health Integration

12,470.76

2,267.41

L5

1

87,493.00

11

366

236

Strategic and Planning Lead

5-Dec-22

5-Dec-23

Secondment

76.00

L7

2

117,152.00

Strategy, Planning and Performance

13,410.48

2,438.27

2

93,426.00

12

298

317

Regional Health Partnerships Coordinator (Cultural)

29-Nov-21

29-Nov-23

Permanent

76.00

L5

1

87,493.00

Regional Health Integration

10,184.35

1,851.70

3

96,036.00

13

183

62

Chief Financial Officer, Executive Manager Corporate Services

12-Nov-18

30-Jun-23

12-Nov-23

Executive Contract

76.00

s

#N/A

Senior Executive Leadership Team

0

#N/A

0.00

L6

1

99,357.00

14

106

33

Regional Health Partnerships Lead (Cultural)

30-Oct-17

30-Oct-23

Permanent

76.00

L7

3

121,830.00

Regional Health Integration

13,630.41

2,478.26

2

105,290.00

15

345

305

Quality Coordinator

1-Sep-22

1-Sep-23

Permanent

76.00

L6

1

Corporate Services

11,670.83

2,121.97

3

109,305.00

16

357

311

Strategic and Planning Lead (Suicide Prevention)

19-Sep-22

19-Sep-23

Permanent

76.00

L7

1

111,221.00

Strategy, Planning and Performance

12,965.82

2,357.42

L7

1

111,221.00

17

326

292

Administration Support

7-Jul-22

7-Jul-23

Permanent

76.00

L3

1

Office of the CEO

8,120.86

1,476.52

2

117,152.00

18

11

252

Finance Business Partner

6-Sep-21

6-Sep-23

Permanent

76.00

L6

3

Corporate Services

12,229.10

2,223.47

3

121,830.00

19

325

212

Primary Health Care Lead - Primary Health Development

6-Jul-22

6-Jul-23

Permanent

76.00

L7

1

111,221.00

Regional Health Integration

13,099.88

2,381.80

L8

1

125,793.00

20

88

5

Executive Manager Health Workforce

11-Jul-17

30-Jun-23

11-Jul-23

Executive Contract

76.00

s

#N/A

Senior Executive Leadership Team

0

#N/A

0.00

L8

2

130,360.00

21

312

304

Information Management Officer

14-Feb-22

14-Feb-24

Permanent

76.00

L5

1

Corporate Services

11,285.52

2,051.91

3

134,927.00

22

290

260

People and Culture Officer

23-Aug-21

23-Aug-23

Permanent

76.00

L5

1

Corporate Services

10,359.57

1,883.56

4

139,495.00

Raw Data to flow in worksheets

Cell Formulas
Range	Formula
O2:O22	O2	=IF(L2=1,2,IF(L2=2,3,IF(L2=3,3,IF(L2=4,4,0))))
Q2:Q22	Q2	=IF(J2=76,1,J2/100)*P2
R2:R22	R2	=$Q$2:$Q$99*(1+'Data Simulator'!$B$5)
S21:S22,S14:S19,S2:S12	S2	=(M2/365)*(H2-1-'Data Simulator'!$B$2)
T21:T22,T14:T19,T2:T12	T2	=IF(H2<='Data Simulator'!$B$3,'Raw Data to flow in worksheets'!P2/365('Data Simulator'!$B$3-'Raw Data to flow in worksheets'!H2),R2/365(H2-'Data Simulator'!$B$3))
U21:U22,U14:U19,U2:U12	U2	=IF(H2<='Data Simulator'!$B$3,'Data Simulator'!$B$4-'Data Simulator'!$B$3,'Data Simulator'!$B$4-'Raw Data to flow in worksheets'!H2)*R2/365
V21:V22,V14:V19,V2:V12	V2	=SUM(S2:U2)
W2:W99	W2	='Data Simulator'!$B$6*'Raw Data to flow in worksheets'!$V$2:$V$99
Y2:Y99	Y2	='Data Simulator'!$B$9*'Raw Data to flow in worksheets'!$V$2:$V$99
AB5:AB6,AB20:AB22,AB17:AB18,AB14:AB15,AB11:AB12,AB8:AB9	AB5	=AB4
H2:H22	H2	=EDATE(F2,(DATEDIF(F2,'Data Simulator'!$B$2,"y")+1)*12)
M2:M22	M2	=XLOOKUP(1,($AB$2:$AB$22=K2)*($AC$2:$AC$22=L2),$AD$2:$AD$22)
X2:X22	X2	=V2/(J226)'Data Simulator'!$B$7*'Data Simulator'!$B$8
Z2:Z22	Z2	=SUM(V2:Y2)
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

ChetanPuri · Apr 30, 2023

ChetanPuri said:
Sorry, please see below, somehow I was able to make XLBB work. Also, I was able to make the create the worksheet and will share the source sheet

Employee positions report_31 March 2023-30 April 2023.xlsx
A B C D E F G H I J K L M N O Q R S T U V W X Y Z AA AB AC AD
1 Employee Code Surname Given Position Number Position Title Start Date (with org) Contract Expiry Date Anniversary Date Employee Type Hours/Fortnight (as at EOM, inc flexible arrangements etc) Classification Step Current Salary Division Description Next Step Next Salary Level - Incudes P-T Calculation As Per New EBA July to Anniversary Date Anniversry date to NEW EBA EBA to End of Financial Year Total Salary SUPER A/L Loading Work Cover Salary with Oncost Level Step Salaries
2 348 309 Strategic and Planning Lead (Alcohol and Other Drugs) 5-Sep-22 5-Sep-23 Permanent 76.00 L7 2 117,152.00 Strategy, Planning and Performance 3 121,830.00 126,703.20 20,862.68 39,386.14 62,830.90 123,079.72 13,538.77 497.05 2,461.59 139,577.14 L1 59,097.00
3 305 57 Primary Health Development Coordinator 5-Jan-22 5-Jan-24 Permanent 76.00 L6 1 99,357.00 Regional Health Integration 2 105,290.00 109,501.60 50,903.45 1,200.02 53,100.78 105,204.24 11,572.47 424.86 2,104.08 119,305.66 L2 62,516.00
4 319 208 Marketing and Communications Advisor 23-May-22 23-May-24 Permanent 76.00 L6 3 109,305.00 Corporate Services 3 109,305.00 113,677.20 97,625.84 44,536.55 11,834.89 153,997.27 16,939.70 621.91 3,079.95 174,638.83 L3 1 66,732.00
5 355 36 Regional Health Partnerships Lead 12-Sep-22 12-Sep-23 Permanent 76.00 L7 1 111,221.00 Regional Health Integration 2 117,152.00 121,838.08 21,939.48 35,627.05 60,418.34 117,984.87 12,978.34 476.48 2,359.70 133,799.38 L3 2 72,665.00
6 375 308 Strategic Planning Coordinator 13-Mar-23 30-Jun-23 13-Mar-24 Maximum Term 76.00 L6 3 109,305.00 Strategy, Planning and Performance 3 109,305.00 113,677.20 76,363.77 22,424.00 33,947.44 132,735.20 14,600.87 536.05 2,654.70 150,526.82 L3 3 75,275.00
7 100 72 Administration Support Officer 4-Sep-17 4-Sep-23 Permanent 60.80 L3 3 75,275.00 Office of the CEO 3 45,767.20 47,597.89 13,198.90 24,541.71 23,603.34 61,343.95 6,747.83 309.67 1,226.88 69,628.34 L4 1 77,112.00
8 131 30 Executive Coordinator 7-Feb-18 7-Feb-24 Permanent 76.00 L4 2 83,045.00 Strategy, Planning and Performance 3 85,655.00 89,081.20 50,054.52 9,030.15 35,144.36 94,229.03 10,365.19 380.54 1,884.58 106,859.35 L4 2 83,045.00
9 221 295 Procurement Business Partner 19-Sep-19 19-Sep-23 Permanent 76.00 L6 2 105,290.00 Corporate Services 3 109,305.00 113,677.20 22,788.79 31,144.44 56,371.43 110,304.67 12,133.51 445.46 2,206.09 125,089.73 L4 3 85,655.00
10 195 37 Regional Health Partnerships Coordinator 4-Feb-19 4-Feb-24 Permanent 76.00 L6 1 99,357.00 Regional Health Integration 2 105,290.00 109,501.60 59,069.78 10,200.15 44,100.64 113,370.57 12,470.76 457.84 2,267.41 128,566.59 L5 1 87,493.00
11 366 236 Strategic and Planning Lead 5-Dec-22 5-Dec-23 Secondment 76.00 L7 2 117,152.00 Strategy, Planning and Performance 3 121,830.00 126,703.20 50,070.44 9,012.08 62,830.90 121,913.43 13,410.48 492.34 2,438.27 138,254.52 L5 2 93,426.00
12 298 317 Regional Health Partnerships Coordinator (Cultural) 29-Nov-21 29-Nov-23 Permanent 76.00 L5 1 87,493.00 Regional Health Integration 2 93,426.00 97,163.04 35,956.03 8,446.73 48,182.22 92,584.98 10,184.35 373.90 1,851.70 104,994.93 L5 3 96,036.00
13 183 62 Chief Financial Officer, Executive Manager Corporate Services 12-Nov-18 30-Jun-23 12-Nov-23 Executive Contract 76.00 s #N/A Senior Executive Leadership Team 0 #N/A #N/A 0.00 0.00 0.00 0.00 L6 1 99,357.00
14 106 33 Regional Health Partnerships Lead (Cultural) 30-Oct-17 30-Oct-23 Permanent 76.00 L7 3 121,830.00 Regional Health Integration 3 121,830.00 126,703.20 40,053.70 21,028.19 62,830.90 123,912.79 13,630.41 500.42 2,478.26 140,521.87 L6 2 105,290.00
15 345 305 Quality Coordinator 1-Sep-22 1-Sep-23 Permanent 76.00 L6 1 99,357.00 Corporate Services 2 105,290.00 109,501.60 16,604.87 35,192.82 54,300.79 106,098.48 11,670.83 428.47 2,121.97 120,319.76 L6 3 109,305.00
16 357 311 Strategic and Planning Lead (Suicide Prevention) 19-Sep-22 19-Sep-23 Permanent 76.00 L7 1 111,221.00 Strategy, Planning and Performance 2 117,152.00 121,838.08 24,072.49 33,380.30 60,418.34 117,871.12 12,965.82 476.02 2,357.42 133,670.39 L7 1 111,221.00
17 326 292 Administration Support 7-Jul-22 7-Jul-23 Permanent 76.00 L3 1 66,732.00 Office of the CEO 2 72,665.00 75,571.60 914.14 35,436.63 37,475.23 73,826.00 8,120.86 298.14 1,476.52 83,721.52 L7 2 117,152.00
18 11 252 Finance Business Partner 6-Sep-21 6-Sep-23 Permanent 76.00 L6 3 109,305.00 Corporate Services 3 109,305.00 113,677.20 19,764.74 35,037.49 56,371.43 111,173.67 12,229.10 448.97 2,223.47 126,075.21 L7 3 121,830.00
19 325 212 Primary Health Care Lead - Primary Health Development 6-Jul-22 6-Jul-23 Permanent 76.00 L7 1 111,221.00 Regional Health Integration 2 117,152.00 121,838.08 1,218.86 57,452.62 60,418.34 119,089.82 13,099.88 480.94 2,381.80 135,052.44 L8 1 125,793.00
20 88 5 Executive Manager Health Workforce 11-Jul-17 30-Jun-23 11-Jul-23 Executive Contract 76.00 s #N/A Senior Executive Leadership Team 0 #N/A #N/A 0.00 0.00 0.00 0.00 L8 2 130,360.00
21 312 304 Information Management Officer 14-Feb-22 14-Feb-24 Permanent 76.00 L5 1 87,493.00 Corporate Services 2 93,426.00 97,163.04 54,413.45 11,712.80 36,469.42 102,595.67 11,285.52 414.33 2,051.91 116,347.44 L8 3 134,927.00
22 290 260 People and Culture Officer 23-Aug-21 23-Aug-23 Permanent 76.00 L5 1 87,493.00 Corporate Services 2 93,426.00 97,163.04 12,464.76 33,530.98 48,182.22 94,177.95 10,359.57 380.33 1,883.56 106,801.42 L8 4 139,495.00
Raw Data to flow in worksheets
Cell Formulas
Range Formula
O2:O22 O2 =IF(L2=1,2,IF(L2=2,3,IF(L2=3,3,IF(L2=4,4,0))))
Q2:Q22 Q2 =IF(J2=76,1,J2/100)*P2
R2:R22 R2 =$Q$2:$Q$99*(1+'Data Simulator'!$B$5)
S21:S22,S14:S19,S2:S12 S2 =(M2/365)*(H2-1-'Data Simulator'!$B$2)
T21:T22,T14:T19,T2:T12 T2 =IF(H2<='Data Simulator'!$B$3,'Raw Data to flow in worksheets'!P2/365*('Data Simulator'!$B$3-'Raw Data to flow in worksheets'!H2),R2/365*(H2-'Data Simulator'!$B$3))
U21:U22,U14:U19,U2:U12 U2 =IF(H2<='Data Simulator'!$B$3,'Data Simulator'!$B$4-'Data Simulator'!$B$3,'Data Simulator'!$B$4-'Raw Data to flow in worksheets'!H2)*R2/365
V21:V22,V14:V19,V2:V12 V2 =SUM(S2:U2)
W2:W99 W2 ='Data Simulator'!$B$6*'Raw Data to flow in worksheets'!$V$2:$V$99
Y2:Y99 Y2 ='Data Simulator'!$B$9*'Raw Data to flow in worksheets'!$V$2:$V$99
AB5:AB6,AB20:AB22,AB17:AB18,AB14:AB15,AB11:AB12,AB8:AB9 AB5 =AB4
H2:H22 H2 =EDATE(F2,(DATEDIF(F2,'Data Simulator'!$B$2,"y")+1)*12)
M2:M22 M2 =XLOOKUP(1,($AB$2:$AB$22=K2)*($AC$2:$AC$22=L2),$AD$2:$AD$22)
X2:X22 X2 =V2/(J2*26)*'Data Simulator'!$B$7*'Data Simulator'!$B$8
Z2:Z22 Z2 =SUM(V2:Y2)
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

Employee positions report_31 March 2023-30 April 2023.xlsx

A

B

1

Description

2

New Financial Year

1-Jul-23

3

EBA

1-Jan-24

4

EOFY

30-Jun-24

5

EBA

4%

6

SUPER

11%

7

A/L Leave Loading

17.50%

8

No.of HRS Leave Loading

45.60

9

Work Cover

2%

Data Simulator

Cell Formulas
Range	Formula
B8	B8	=6*7.6

If statement

ChetanPuri

Board Regular

Excel Facts

awoohaw

Well-known Member

awoohaw

Well-known Member

awoohaw

Well-known Member

ChetanPuri

Board Regular

ChetanPuri

Board Regular

Attachments

ChetanPuri

Board Regular

awoohaw

Well-known Member

ChetanPuri

Board Regular

ChetanPuri

Board Regular

Similar threads

Forum statistics

Share this page

Cell Formulas
Range	Formula
R5:R6,R20:R22,R17:R18,R14:R15,R11:R12,R8:R9	R5	=R4
H2:H9	H2	=DATE(IF(DATE(YEAR($H$1),MONTH(F2),DAY(F2))>$H$1,YEAR($H$1)-1,$H$1),MONTH(F2),DAY(F2))
P2:P12,P24:P27,P21:P22,P14:P19	P2	=IF(L2="1",K2,INDEX($T$2:$T$22,MATCH(K2&L2,$R$2:$R$22&$S$2:$S$22,0)+1))
O2:O27	O2	=IF(L2="1",K2,INDEX($S$2:$S$22,MATCH(K2&L2,$R$2:$R$22&$S$2:$S$22,0)+1))

If statement

Board Regular

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Board Regular

Board Regular

Attachments

Board Regular

Well-known Member

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock