Complex Formula

[Guest]

Im trying to setup a table with a max of 35 cells across and down, which may or may not ever be filled completely. Both have a fixed dollar amounts in which either number maybe be higher or lower than the other. I need the table to apply the amount from the persons amount to the house filling in the table across and down.. Columns are values of work done in a house, rows are people that did the work and have the amount of money they get for the week. The total of houses and people will always match, and i tried using a formula that just uses a percentage and applies it to all houses..but for some data entry reason, its needed to try to keep the amount applied against the houses row to a minimum. Im thinking the if formula, but i think that it would require nesting beyond the commands 7 limit. Any suggestions? Below is how it would actually look on paper.

Houses: 300.00 100.00 500.00 125.00 200.00
People Ed 350.00 300.00 50.00 0 0 0
Sal 125.00 0 50.00 75.00 0 0
Tim 525.00 0 0 450.00 125.00 0
Jim 200.00 0 0 0 0 200.00

 

[Bernie Deitrick]

Morrow,

There is no way for you to write a formula to do that. They are
infinitely many ways that you can distribute the money from the houses
to the people. You'll just have to do it manually.

HTH,
Bernie
MS Excel MVP

Im trying to setup a table with a max of 35 cells across and down,

which may or may not ever be filled completely. Both have a fixed
dollar amounts in which either number maybe be higher or lower than
the other. I need the table to apply the amount from the persons
amount to the house filling in the table across and down.. Columns are
values of work done in a house, rows are people that did the work and
have the amount of money they get for the week. The total of houses
and people will always match, and i tried using a formula that just
uses a percentage and applies it to all houses..but for some data
entry reason, its needed to try to keep the amount applied against the
houses row to a minimum. Im thinking the if formula, but i think that
it would require nesting beyond the commands 7 limit. Any suggestions?
Below is how it would actually look on paper.

Houses: 300.00 100.00 500.00 125.00 200.00
People Ed 350.00 300.00 50.00 0 0 0
Sal 125.00 0 50.00 75.00 0 0
Tim 525.00 0 0 450.00 125.00 0
Jim 200.00 0 0

0 0 200.00

 

[Harlan Grove]

Im trying to setup a table with a max of 35 cells across and down,
which may or may not ever be filled completely. Both have a fixed
dollar amounts in which either number maybe be higher or lower than
the other. I need the table to apply the amount from the persons amount
to the house filling in the table across and down.. Columns are values
of work done in a house, rows are people that did the work and have the
amount of money they get for the week. The total of houses and people
will always match, and i tried using a formula that just uses a
percentage and applies it to all houses..but for some data entry
reason, its needed to try to keep the amount applied against the houses
row to a minimum. Im thinking the if formula, but i think that it would
require nesting beyond the commands 7 limit. Any suggestions? Below is
how it would actually look on paper.

...

Are you a contractor trying to allocate what you've paid your workers to the
houses you're working on? If so, don't you keep records of how many hours your
workers spend working on each house?

 

[Guest]

Subcontracting, where workers are basically paid by the number of boards they install per house with their team leads determining how much to pay each person. The inventory and accounting system the customer has... amounts have to be applied to the houses in total. Im just trying to help them automate some data entry that takes them 1.5 days to calculate then enter, a spread sheet that takes it up to the point where im having problems is saving them 8hrs right now. They didnt like me doing the table as a percentage, because then data entry after the spreadsheet means they enter $$ amounts for each house for each worker, instead of one or two houses. Their accounting system is a custom app that doesnt have this feature and it looks like they can get it included..thus the spreadsheet thing. I will figure it out, even it if takes 5 or 6 cells doing if's etc, I had just hoped that someone might have seen another method than the one im looking at... thanks anyway folks.