Help With Ideas- Conditional formulas

mbpaul1987

New Member
Joined
Jan 15, 2014
Messages
15
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:
NameCategory 1Category 2Category 3Category 4Rate DateHourly RateBill Rate
JoeYesYesYesYesJan-20192025.75 (Formula 1 & Jan-2019 bill rates)
FrankYesNoNoYesJun-20192022.70 (Formula 2 & Jun-2019 bill rates)
HarryYesYesYesYesJun-20192026.95 (Formula 1 & Jun-2019 bill rates)
BobYesNoNoNoJan-20202024.20 (Formula 3 & Jan-2020 bill rates)

Bill Rates
Rate DatePension RatioSavingsEmployment Insurance Ratio (E.I.)Education FundUnion 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 NumberCategory 1Category 2Category 3Category 4Formula
1YesYesYesYes=Hourly Rate + (Hourlyrate*Pension Ratio)+Savings+(Hourlyrate*E.I. Ratio)+Education Fund+Union dues
2YesNoNoYes=Hourly Rate + (Hourlyrate*Pension Ratio)+(Hourlyrate*E.I. Ratio)+Union dues
3YesNoNoNo=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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Upvote 0
It's a little tricky, but you can do it with a formula:

Book5
ABCDEFGH
1NameCategory 1Category 2Category 3Category 4Rate DateHourly RateBill Rate
2JoeYesYesYesYesJan-201920 $ 25.75
3FrankYesNoNoYesJun-201920 $ 22.70
4HarryYesYesYesYesJun-201920 $ 26.95
5BobYesNoNoNoJan-202020 $ 24.20
6
7
8Rate DatePension RatioSavingsEmployment Insurance Ratio (E.I.)Education FundUnion Dues
9Jan-20190.04 $ 2.00 0.06 $ 1.50 $ 0.25
10Jun-20190.05 $ 2.50 0.07 $ 1.75 $ 0.30
11Jan-20200.06 $ 3.00 0.08 $ 2.00 $ 0.35
12
13
14Formula NumberCategory 1Category 2Category 3Category 4Formula
151YesYesYesYes11111
162YesNoNoYes10101
173YesNoNoNo11000
18
Sheet10
Cell Formulas
RangeFormula
H2:H5H2=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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
FWIW, I get the feeling that Access would be far & away preferable to Excel for this task
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top