I need to adjust a machine setting according to a 5% average balance between
4 cranes storing pallets.

If the average of 76.12% is 2.5% above the total average of all cranes, then
adjust down to 1.
If the average of 76.12% is -2.5% less then the average of all cranes, then
adjust up to 1.

Aisle1 Aisle2 Aisle 3 Aisle4 Totals

4,092 4,128 4,128 4,128 16,476 Total #Plts
3,115 3,218 3,473 3,420 13,226 Actual # Plts
76.12% 77.96% 84.13% 82.85% 80.27% Avg % Full

82.28% is 2.50% of 80.27%

78.27% is -2.50% of 80.27%
I have worked on this for 3 days and cant get
the formula set. I'm using Excel VS.2003.

I am unsure of what "move up 1" means in this situation, but there are some
formulas that may help, such as the max and min functions.

I made these using the percentages that you gave in cells B23:E23. the
formulas reference themselves, and they are in cells J30:J35 and cells K30:K34
this may give you a start, and clarification of the problem may bring better
help.

=MAX(B30:E30) =AVERAGE(B30:E30)
=MIN(B30:E30) =MAX(B30:E30)-K30
=MAX(J30:J31)/SUM(J30:J31) =K30-MIN(B30:E30)
=MIN(J30:J31)/SUM(J30:J31) =K31/K30
=(J30-J31)/J30 =K32/K30
=(J30-J31)/J31

The formated results of the above formulas look like this:
84.13% 80.27%
76.12% 3.87%
52.50% 4.14%
47.50% 4.818%
9.519% 5.159%
10.520%

Anyway, I grabbed the maximum and minimum number and compared them with each
other and also compared them with the overall average. The key is in using
MAX() and MIN() instead of greater than and less than. That automatically
gets the problem aisle/cranes and shows you right away if an adjustment even
needs to be made. A lookup function or something like it might allow you to
automatically detect which is the max and min, but it might not be cost
effective (your developement time-wise) with so few aisles.

Hope this helps, more details may be needed if it doesn't help, unless
someone else already understands what you are doing from experience with that
kind of problem.
SongBear

"Candace" wrote:

I need to adjust a machine setting according to a 5% average balance between
4 cranes storing pallets.

If the average of 76.12% is 2.5% above the total average of all cranes, then
adjust down to 1.
If the average of 76.12% is -2.5% less then the average of all cranes, then
adjust up to 1.

Aisle1 Aisle2 Aisle 3 Aisle4 Totals

4,092 4,128 4,128 4,128 16,476 Total #Plts
3,115 3,218 3,473 3,420 13,226 Actual # Plts
76.12% 77.96% 84.13% 82.85% 80.27% Avg % Full

82.28% is 2.50% of 80.27%

78.27% is -2.50% of 80.27%
I have worked on this for 3 days and cant get
the formula set. I'm using Excel VS.2003.

The 'move up 1' means a dial, our cranes base is set at 4's,(crane will store
4 pallets before the conveyor will send the next 4 to the next crane)
therefore I need to keep our cranes in balance for storing pallets. I dont
want one crane filling up quicker then others, therefore if I have a formula
to tell the operator when to adjust the machine down a notch or up a notch
this should keep the %full within a 5% balance.

"SongBear" wrote:

I am unsure of what "move up 1" means in this situation, but there are some
formulas that may help, such as the max and min functions.

I made these using the percentages that you gave in cells B23:E23. the
formulas reference themselves, and they are in cells J30:J35 and cells K30:K34
this may give you a start, and clarification of the problem may bring better
help.

=MAX(B30:E30) =AVERAGE(B30:E30)
=MIN(B30:E30) =MAX(B30:E30)-K30
=MAX(J30:J31)/SUM(J30:J31) =K30-MIN(B30:E30)
=MIN(J30:J31)/SUM(J30:J31) =K31/K30
=(J30-J31)/J30 =K32/K30
=(J30-J31)/J31

The formated results of the above formulas look like this:
84.13% 80.27%
76.12% 3.87%
52.50% 4.14%
47.50% 4.818%
9.519% 5.159%
10.520%

Anyway, I grabbed the maximum and minimum number and compared them with each
other and also compared them with the overall average. The key is in using
MAX() and MIN() instead of greater than and less than. That automatically
gets the problem aisle/cranes and shows you right away if an adjustment even
needs to be made. A lookup function or something like it might allow you to
automatically detect which is the max and min, but it might not be cost
effective (your developement time-wise) with so few aisles.

Hope this helps, more details may be needed if it doesn't help, unless
someone else already understands what you are doing from experience with that
kind of problem.
SongBear

"Candace" wrote:

I need to adjust a machine setting according to a 5% average balance between
4 cranes storing pallets.

If the average of 76.12% is 2.5% above the total average of all cranes, then
adjust down to 1.
If the average of 76.12% is -2.5% less then the average of all cranes, then
adjust up to 1.

Aisle1 Aisle2 Aisle 3 Aisle4 Totals

4,092 4,128 4,128 4,128 16,476 Total #Plts
3,115 3,218 3,473 3,420 13,226 Actual # Plts
76.12% 77.96% 84.13% 82.85% 80.27% Avg % Full

82.28% is 2.50% of 80.27%

78.27% is -2.50% of 80.27%
I have worked on this for 3 days and cant get
the formula set. I'm using Excel VS.2003.

OK, try this. The formulas used we (note that the formulas wrapped in this
post)

=IF(Asile1-AvgAll<-Allowed,"Adjust up 1",IF(AvgAll-Asile1<-Allowed,"Adjust
down 1","Crane OK"))

=IF(Asile2-AvgAll<-Allowed,"Adjust up 1",IF(AvgAll-Asile2<-Allowed,"Adjust
down 1","Crane OK"))

=IF(Asile3-AvgAll<-Allowed,"Adjust up 1",IF(AvgAll-Asile3<-Allowed,"Adjust
down 1","Crane OK"))

=IF(Asile4-AvgAll<-Allowed,"Adjust up 1",IF(AvgAll-Asile4<-Allowed,"Adjust
down 1","Crane OK"))

Note that I named ranges, hoping to make the formulas easier to read. The
(mispelled) aisle1, aisle2, etc were the percent cells, i.e. the one with
76.12% was named "Asile1", the one with 77.96% was "Asile2", the one with
80.27% was "AvgAll"...I also added a new named range "Allowed" where i stored
the .025 allowed difference. Doing it this way, if the standard changes, you
can change that one number without changing the rest of the formulas.

The results looked like this:
Allowed
Aisle1 Aisle2 Aisle3 Aisle4 Totals 0.025
4,092 4,128 4,128 4,128 16,476 Total#Plts
3,115 3,218 3,473 3,420 13,226 Actual#Plts

76.12% 77.96% 84.13% 82.85% 80.27% Avg%Full
Crane# Formula Result:
Crane 1 Adjust up 1
Crane 2 Crane OK
Crane 3 Adjust down 1
Crane 4 Adjust down 1

Hope this helps,
SongBear

"Candace" wrote:

The 'move up 1' means a dial, our cranes base is set at 4's,(crane will store
4 pallets before the conveyor will send the next 4 to the next crane)
therefore I need to keep our cranes in balance for storing pallets. I dont
want one crane filling up quicker then others, therefore if I have a formula
to tell the operator when to adjust the machine down a notch or up a notch
this should keep the %full within a 5% balance.

"SongBear" wrote:

I am unsure of what "move up 1" means in this situation, but there are some
formulas that may help, such as the max and min functions.

I made these using the percentages that you gave in cells B23:E23. the
formulas reference themselves, and they are in cells J30:J35 and cells K30:K34
this may give you a start, and clarification of the problem may bring better
help.

=MAX(B30:E30) =AVERAGE(B30:E30)
=MIN(B30:E30) =MAX(B30:E30)-K30
=MAX(J30:J31)/SUM(J30:J31) =K30-MIN(B30:E30)
=MIN(J30:J31)/SUM(J30:J31) =K31/K30
=(J30-J31)/J30 =K32/K30
=(J30-J31)/J31

The formated results of the above formulas look like this:
84.13% 80.27%
76.12% 3.87%
52.50% 4.14%
47.50% 4.818%
9.519% 5.159%
10.520%

Anyway, I grabbed the maximum and minimum number and compared them with each
other and also compared them with the overall average. The key is in using
MAX() and MIN() instead of greater than and less than. That automatically
gets the problem aisle/cranes and shows you right away if an adjustment even
needs to be made. A lookup function or something like it might allow you to
automatically detect which is the max and min, but it might not be cost
effective (your developement time-wise) with so few aisles.

Hope this helps, more details may be needed if it doesn't help, unless
someone else already understands what you are doing from experience with that
kind of problem.
SongBear

"Candace" wrote:

I need to adjust a machine setting according to a 5% average balance between
4 cranes storing pallets.

If the average of 76.12% is 2.5% above the total average of all cranes, then
adjust down to 1.
If the average of 76.12% is -2.5% less then the average of all cranes, then
adjust up to 1.

Aisle1 Aisle2 Aisle 3 Aisle4 Totals

4,092 4,128 4,128 4,128 16,476 Total #Plts
3,115 3,218 3,473 3,420 13,226 Actual # Plts
76.12% 77.96% 84.13% 82.85% 80.27% Avg % Full

82.28% is 2.50% of 80.27%

78.27% is -2.50% of 80.27%
I have worked on this for 3 days and cant get
the formula set. I'm using Excel VS.2003.