Brain Teaser

[John McCabe]

A small example follows. the avg. rate is 17.00 for dept. Want to move
average rate up to 17.51 (3% jump). To make it easy want to change the rate
for the highest rate or highest hours rate.
(Row) (Col b) (Col c) (Col d)
3) Hours Rate Total
4) 10400 17.20 178,880
5) 2080 16.95 35,256
1560 15.75 24,570
T O T A L
14040 17.00 238,706
I have tried using max for highest, vlookup and use the max for the formula
but cannot get the thing to work for either dollars or hours. Just want to
increase ONE so total avg. is 17.51..

 

[John Bundy]

If this is just a one-time thing I would use Goal Seek. Tools->Goal Seek->Set
Cell=cell that contains 17.00, To Value= 17.51, By Changing Cell= the cell
you want to change to get the result such as the 17.20 rate.

 

[John McCabe]

WOW - never used goal seek, have to keep that one in mind.
Actually it is for about 145 depts so fix does not work easy.
BUT maybe I can write a macro to pull the "Goal Seek" into action. Minor
hassle, not easy to know what the new average rate should be to change it
to..
But thank you for the goal seek - never tried it before.