Hello There!
Here’s what I’m trying to do:
For a landscaping company, who is looking to service gardens more effectively/efficiently, they are mirroring the below variables to the amount the client is willing to pay per hour. And let me first say if any of this doesn’t make any sense or needs clarification, let me know or private message me. I also have a sample file which may be of help. Just not sure how to send it, if I can, or not.
Thank you! Please read on:
I have, on Sheet1, 8 columns I’m working with:
R3:R132 is the currently hourly rate
L3:L132, M3:M132, N3:N132 are current units, volume, and labor hours respectively by location.
AD3:AD132, AE3:AE132, AF3:AF132 are new units, volume, and labor hours respectively by location.
AI3:AI132 is the suggested hourly rate by location. Note this is already calculated and filled in. I need to calculate new units, volume, and labor hours based on the suggested hourly rate.
On Sheet2, I have 4 columns I’m working with:
A2:A161, B2:B161, C2:C161, B2:B161 are hourly rates, units, volume, and labor hours respectively.
They are laid out in combination with one another to show possible combinations of these variables that make sense for the landscaping company.
My goal is to figure out what combination the company wants to go with for a given hourly rate on Sheet1 given the following logic to follow:
1) Attempt to reduce labor hours to get to suggested hourly rate – This is to calculate new labor hours on Sheet1 in cells AF3:AF132.
If
If suggested hourly rate = current hourly rate keep same units, volume, and labor hours
Else if labor hours is <=.25, move to step 2
else find a suggested hourly rate on Sheet2 that has labor hours of current labor hours/2
else find a suggested hourly rate on Sheet2 that has labor hours of current labor hours -1
else find a suggested hourly rate on Sheet2 that has labor hours of current labor hours -2
else find a suggested hourly rate on Sheet2 that has labor hours of current labor hours -3
end if
else
2) Attempt to reduce units to get to get to suggested hourly rate – This is to calculate new units on Sheet1 in cells AD3:AD132
if units = 1, move to step 3
else find a suggested hourly rate on Sheet2 that has units of current units/2
else find a suggested hourly rate on Sheet2 that has units of current units -1
else find a suggested hourly rate on Sheet2 that has units of current units -2
else find a suggested hourly rate on Sheet2 that has units of current units -3
else find a suggested hourly rate on Sheet2 that has units of current units -4
else find a suggested hourly rate on Sheet2 that has units of current units -5
end if
else
3) Attempt to reduce volume to get to suggested hourly rate – This is to calculate new volume on Sheet1 in cells AE3:AE132
if volume <=2, keep current units, volume, and labor hours
else find a suggested hourly rate on Sheet2 that has volume of current volume/2
else find a suggested hourly rate on Sheet2 that has volume of current volume-1
else find a suggested hourly rate on Sheet2 that has volume of current volume-2
else find a suggested hourly rate on Sheet2 that has volume of current volume-3
else find a suggested hourly rate on Sheet2 that has volume of current volume-4
end if
else
keep current units, volume, and labor hours
end if
Note I have a current formula which I’ve been working at which ALMOST calculates the new labor hours, but sometimes it generates an #N/A. No idea how to calculate all three variables at the same time. Maybe a concatenation of some sort and then a left right formula to separate it back out?
Again, if any of this doesn’t make sense or needs clarification, let me know or Private message me.
Thank you very much!
Here’s what I’m trying to do:
For a landscaping company, who is looking to service gardens more effectively/efficiently, they are mirroring the below variables to the amount the client is willing to pay per hour. And let me first say if any of this doesn’t make any sense or needs clarification, let me know or private message me. I also have a sample file which may be of help. Just not sure how to send it, if I can, or not.
Thank you! Please read on:
I have, on Sheet1, 8 columns I’m working with:
R3:R132 is the currently hourly rate
L3:L132, M3:M132, N3:N132 are current units, volume, and labor hours respectively by location.
AD3:AD132, AE3:AE132, AF3:AF132 are new units, volume, and labor hours respectively by location.
AI3:AI132 is the suggested hourly rate by location. Note this is already calculated and filled in. I need to calculate new units, volume, and labor hours based on the suggested hourly rate.
On Sheet2, I have 4 columns I’m working with:
A2:A161, B2:B161, C2:C161, B2:B161 are hourly rates, units, volume, and labor hours respectively.
They are laid out in combination with one another to show possible combinations of these variables that make sense for the landscaping company.
My goal is to figure out what combination the company wants to go with for a given hourly rate on Sheet1 given the following logic to follow:
1) Attempt to reduce labor hours to get to suggested hourly rate – This is to calculate new labor hours on Sheet1 in cells AF3:AF132.
If
If suggested hourly rate = current hourly rate keep same units, volume, and labor hours
Else if labor hours is <=.25, move to step 2
else find a suggested hourly rate on Sheet2 that has labor hours of current labor hours/2
else find a suggested hourly rate on Sheet2 that has labor hours of current labor hours -1
else find a suggested hourly rate on Sheet2 that has labor hours of current labor hours -2
else find a suggested hourly rate on Sheet2 that has labor hours of current labor hours -3
end if
else
2) Attempt to reduce units to get to get to suggested hourly rate – This is to calculate new units on Sheet1 in cells AD3:AD132
if units = 1, move to step 3
else find a suggested hourly rate on Sheet2 that has units of current units/2
else find a suggested hourly rate on Sheet2 that has units of current units -1
else find a suggested hourly rate on Sheet2 that has units of current units -2
else find a suggested hourly rate on Sheet2 that has units of current units -3
else find a suggested hourly rate on Sheet2 that has units of current units -4
else find a suggested hourly rate on Sheet2 that has units of current units -5
end if
else
3) Attempt to reduce volume to get to suggested hourly rate – This is to calculate new volume on Sheet1 in cells AE3:AE132
if volume <=2, keep current units, volume, and labor hours
else find a suggested hourly rate on Sheet2 that has volume of current volume/2
else find a suggested hourly rate on Sheet2 that has volume of current volume-1
else find a suggested hourly rate on Sheet2 that has volume of current volume-2
else find a suggested hourly rate on Sheet2 that has volume of current volume-3
else find a suggested hourly rate on Sheet2 that has volume of current volume-4
end if
else
keep current units, volume, and labor hours
end if
Note I have a current formula which I’ve been working at which ALMOST calculates the new labor hours, but sometimes it generates an #N/A. No idea how to calculate all three variables at the same time. Maybe a concatenation of some sort and then a left right formula to separate it back out?
Code:
=IF(AI3=R3,N3,IF(N3<=0.25,N3,IF(AND(N3/2=VLOOKUP(AI3,'Sheet2'!A:D,4,FALSE),L3=VLOOKUP(AI3,'Sheet2'!A:D,2,FALSE),M3=VLOOKUP(AI3,'Sheet2'!A:D,3,FALSE)),N3/2,IF(AND(N3-1=VLOOKUP(AI3,'Sheet2'!A:D,4,FALSE),L3=VLOOKUP(AI3,'Sheet2'!A:D,2,FALSE),M3=VLOOKUP(AI3,'Sheet2'!A:D,3,FALSE)),N3-1,IF(AND(N3-2=VLOOKUP(AI3,'Sheet2'!A:D,4,FALSE),L3=VLOOKUP(AI3,'Sheet2'!A:D,2,FALSE),M3=VLOOKUP(AI3,'Sheet2'!A:D,3,FALSE)),N3-2,IF(AND(N3-3=VLOOKUP(AI3,'Sheet2'!A:D,4,FALSE),L3=VLOOKUP(AI3,'Sheet2'!A:D,2,FALSE),M3=VLOOKUP(AI3,'Sheet2'!A:D,3,FALSE)),N3-3,N3))))))Again, if any of this doesn’t make sense or needs clarification, let me know or Private message me.
Thank you very much!
