Help With Ideas- Conditional formulas

[mbpaul1987]

So I have been tasked with developing a solution in either Excel or Access that I have been trying to work out the best approach and I could use help with a few ideas. It's been a few years since I've written VBA and I've been amazed at how much I've lost so I feel like I'm floundering a bit as I think there may be a good way to do it but I can't remember. Here is my requirement:

I need to develop a Bill Rate calculator for employees that are being hired on. I currently have an Employee database with start dates, employee categorization types (5 columns), Rate Date, Hourly rate, Bill rate, + additional columns. The bill rate needs to be calculated based on the categorization types (there are 15 formula variations). That in itself is pretty straight forward however where it is getting complicated is that the rate calculations change every 6 months. So I need to be able to reference 1 of 15 formulas based on the categorization types filled in, then use the rate calculation data for the rate date. As a simple example:

Database:

Name	Category 1	Category 2	Category 3	Category 4	Rate Date	Hourly Rate	Bill Rate
Joe	Yes	Yes	Yes	Yes	Jan-2019	20	25.75 (Formula 1 & Jan-2019 bill rates)
Frank	Yes	No	No	Yes	Jun-2019	20	22.70 (Formula 2 & Jun-2019 bill rates)
Harry	Yes	Yes	Yes	Yes	Jun-2019	20	26.95 (Formula 1 & Jun-2019 bill rates)
Bob	Yes	No	No	No	Jan-2020	20	24.20 (Formula 3 & Jan-2020 bill rates)

Bill Rates

Rate Date	Pension Ratio	Savings	Employment Insurance Ratio (E.I.)	Education Fund	Union Dues
Jan-2019	.04	$2.00	.06	$1.50	$.25
Jun-2019	.05	$2.50	.07	$1.75	$.30
Jan-2020	.06	$3.00	.08	$2.00	$.35

Formulas

Formula Number	Category 1	Category 2	Category 3	Category 4	Formula
1	Yes	Yes	Yes	Yes	=Hourly Rate + (Hourlyrate*Pension Ratio)+Savings+(Hourlyrate*E.I. Ratio)+Education Fund+Union dues
2	Yes	No	No	Yes	=Hourly Rate + (Hourlyrate*Pension Ratio)+(Hourlyrate*E.I. Ratio)+Union dues
3	Yes	No	No	No	=Hourly Rate + (Hourlyrate*Pension Ratio)+Savings

I need to be able to calculate that bill rate in the database. Any assistance with ideas would be greatly appreciated.
Thank You

 

[jasonb75]

Which individual parts of the formula do each of the 4 categories refer to? I thought I had it worked out, but some of the results appear to conflict logic.

 

[Eric W]

It's a little tricky, but you can do it with a formula:

Book5
	A	B	C	D	E	F	G	H
1	Name	Category 1	Category 2	Category 3	Category 4	Rate Date	Hourly Rate	Bill Rate
2	Joe	Yes	Yes	Yes	Yes	Jan-2019	20	$ 25.75
3	Frank	Yes	No	No	Yes	Jun-2019	20	$ 22.70
4	Harry	Yes	Yes	Yes	Yes	Jun-2019	20	$ 26.95
5	Bob	Yes	No	No	No	Jan-2020	20	$ 24.20
6
7
8	Rate Date	Pension Ratio	Savings	Employment Insurance Ratio (E.I.)	Education Fund	Union Dues
9	Jan-2019	0.04	$ 2.00	0.06	$ 1.50	$ 0.25
10	Jun-2019	0.05	$ 2.50	0.07	$ 1.75	$ 0.30
11	Jan-2020	0.06	$ 3.00	0.08	$ 2.00	$ 0.35
12
13
14	Formula Number	Category 1	Category 2	Category 3	Category 4	Formula
15	1	Yes	Yes	Yes	Yes	11111
16	2	Yes	No	No	Yes	10101
17	3	Yes	No	No	No	11000
18
Sheet10

Cell Formulas
Range	Formula
H2:H5	H2	=G2+SUMPRODUCT(INDEX($B$9:$F$11,MATCH(F2,$A$9:$A$11,0),0),--(MID(INDEX($F$15:$F$30,MATCH(B2&C2&D2&E2,$B$15:$B$30&$C$15:$C$30&$D$15:$D$30&$E$15:$E$30,0)),{1,2,3,4,5},1)),IF({0,1,0,1,1},1,G2))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

In your formula table, you put in a 5 digit number of 1's and 0's signifying which of the 5 elements to include. You should make the F15:F17 range Text format so that preceding zeros aren't lost.

Then the H2 formula looks up the Rates, and multiplies them by the 1/0 from the formula, and finally by a 1 or the hourly rate depending on whether it's an amount or a percentage.

Anyway, this is one thought. Maybe it will help.

 

[mbpaul1987]

Which individual parts of the formula do each of the 4 categories refer to? I thought I had it worked out, but some of the results appear to conflict logic.

That is hard to specify, Some have them 1 to 1 (I have a column that is called direct/indirect and that will require a specific charge) however others may have them that 1 criteria adds 5 elements to the formula but these could be modified by a third element.

 

[mbpaul1987]

It's a little tricky, but you can do it with a formula:

In your formula table, you put in a 5 digit number of 1's and 0's signifying which of the 5 elements to include. You should make the F15:F17 range Text format so that preceding zeros aren't lost.

Then the H2 formula looks up the Rates, and multiplies them by the 1/0 from the formula, and finally by a 1 or the hourly rate depending on whether it's an amount or a percentage.

Anyway, this is one thought. Maybe it will help.

Thanks that make alot of sense and I think it might work for what I need. Unfortunately I am terrible at array formulas so it might be a bit interesting . Thanks for the help I will let you know how it goes

 

[Eric W]

Here's a version of the formula that doesn't require the Control+Shift+Enter:

=G2+SUMPRODUCT($B$9:$F$11*($A$9:$A$11=F2)*(MID(INDEX($F$15:$F$30,MATCH(4,MMULT(--(B2:E2=$B$15:$E$30),{1;1;1;1}),0)),{1,2,3,4,5},1))*IF({0,1,0,1,1},1,G2))

It's also a fair amount shorter than my original offering. It is a bit closer to standard usage of SUMPRODUCT, but then the MMULT and use of array constants in the second part might get a little challenging to grasp. Let me know if you have any questions about how it works.

 

[mbpaul1987]

Here's a version of the formula that doesn't require the Control+Shift+Enter:

=G2+SUMPRODUCT($B$9:$F$11*($A$9:$A$11=F2)*(MID(INDEX($F$15:$F$30,MATCH(4,MMULT(--(B2:E2=$B$15:$E$30),{1;1;1;1}),0)),{1,2,3,4,5},1))*IF({0,1,0,1,1},1,G2))

It's also a fair amount shorter than my original offering. It is a bit closer to standard usage of SUMPRODUCT, but then the MMULT and use of array constants in the second part might get a little challenging to grasp. Let me know if you have any questions about how it works.

Hi Eric,

Thank you for your help! Your first formula worked perfectly once I figured out how to use it. I was able to adapt it to my specific application. I actually didn't even see your second posting until after I had finished but I will keep it in mind in case this comes up again.

Many thanks again for your help
Michael

 

[Eric W]

I'm just happy you got it working! Thanks for the update.

 

[Fazza]

FWIW, I get the feeling that Access would be far & away preferable to Excel for this task