Newsroom Update

New features come to Apple services this fall. Learn more >

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

lookup formula with math I don't know.lol

ok so I have a spreadsheet comprised of 5 different sheets....


sheet 1-4 are identical pay roll reports (one for each employee)



sheet 5 is a list of all customers and the expected hours the job should take



Now... currently on sheets 1-4 I manually input the clients my employees worked that week. I then use the formula below in column "G" of this sheet to match the customers I put in, to the master list of customers on sheet 5. this automatically scrubs the customers and puts the job hours from sheet 5 in the appropriate cell of column G of sheet 1-4


XLOOKUP(B10,Customers::Table 1::A,'Job Hours quoted ',"")



now in lies the problem...


often times I have multiple people on a job and they split the estimated job hour time... so what I need is a formula that reads all sheets 1-4 and auto fills like it currently does, but IF the same client is listed on another persons sheet I want the total number returned divided by the number of people on the job. this way if 2 people go to the job, Cell "G" of the time sheet will turn an 8 hour job into 4 hours each, or if I have 3 people on the same client it will then turn that same job into 2.66 hours each...


all other formulas work just fine I just can't figure out the equation to get all cells read and the adjusted time correct

Posted on Jan 21, 2022 6:00 PM

Reply
Question marked as Best reply

Posted on Jan 21, 2022 7:34 PM

See my response to the earlier post of this question: https://discussions.apple.com/thread/253590724


Regards,

Barry

Similar questions

1 reply

lookup formula with math I don't know.lol

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.