Student needs help with formula

[ctec4rxj]

Hi

I can't for the life of me figure out how to go across and increase the values by the amount listed in B55:B66.

r67 should =200 points (this is the sum of colums d,f,h,j,l,n,p and R)

I really hope this makes sense. Sorry been banging my head on this for a few hours.

I tried B55*vlookup but it does not increase the whole row by 10%

Thank in advance. This is for a class that i am in.

[ctec4rxj]

Deleted myself

[ctec4rxj]

deleted myself

[davsth]

Its not clear what you are asking, are you wanting to increase the values in the a columns so that the lookup returns higher values that then in turn sum to 200

this will not solve with the example as so may values are the same so would jump category at the same point to an answer doesn't exist
multiplying all values by 1.999 gives 194, by 2 gives 205 as you have 8 100s, 2 50s and 1 25 in the sample date and they all move up a category at the same time to make the total 11 larger.

I assume the values have to be integers?


or is it multiplying all the whole number columsn by 200/108 to they sum to 200?

[Pepe Le Mokko]

Deleted by myself

[ctec4rxj]

Hi

sorry, I am probably overthinking this. The problem states:

assign 200 points into specific categories that are of most importance. Weigh those categories with sub levels based on the factors listed in Exhibit 2. The table is exhibit 2.

[MrShorty]

Still not sure I understand what you are doing?

Which table is Exhibit 2? A54:B67?

Looking at this as more of a math problem than a programming problem, I see:

1) We have 8 sets of values/points. There will be 12 entries in each set. Each entry will be an integer from 0 to 4.
2) The total/sum of all 8 sets will be 200. total points = sum(all entries from 8 sets) = 200
3) The total/sum of the "billable hours" entry in each set will be 10% of total (or 20) total billable = sum(billable entries from 8 sets) = 10%*200=20. A similar statement for each of the 12 categories. Are there other constraints, or will any combination of 8 integers that sum up to 20 be valid? Could we have five 4s and three 0s (4+4+4+4+4+0+0+0=20)?

I don't see how the lookup table (A77:B81) figures into the solution. If I've understood the problem, it looks vaguely like a "subset sum" problem where you find a combination of "points" from the available pool of points (0 to 4) that add up to the sum expected for each category. The main question would be to understand what kind of algorithm you want to use to generate those combinations. You haven't talked about your desired algorithm (perhaps you don't have one in mind, yet?), so it is difficult to know what to recommend. You could use a "greedy" algorithm like I used -- Assign a point value of 4 until you are going to meet/exceed the total for that category, then whatever is needed to get to the total for that category, then 0 for the remaining values. That will result in a lot of 4s and 0s and relatively few of the intermediate scores. I don't know if that is a problem or not -- that's why I asked about other constraints.

I'm not sure exactly how you intend to approach this problem. Have I at least understood it?

[ctec4rxj]

I created this of another template.

this is basically a profit sharing sheet.

Thanks for the input it got me thinking.

[davsth]

it is a way of sharing although the alternative is to get a score over all of the categories
=SUMPRODUCT($C$6:$C$17,C$21:C$32) for each employee

sum the above 8 values and divide them by this gives a percentage share, then they all get that percentage of c4

it all depnds if you want billable hours to share 10% of 216 so 21.6 or these are weightings to share the overall which is the above approach