Help with difficult numbers formula

Question

Level 1

0 points

Help with difficult numbers formula

I need help with a formula im trying to create in numbers on my iPad. I'll try to explaine the best I can and attach images to help.

Ive created a spreadsheet to do rotas for work. Currently it calculates the shifts each person does and adds it to the total for that persons week in the end column and the total for the day in the bottom row. If the shift is equal to or more than 8 hours long then an hour needs to be deducted as they have a lunch hour that is unpaid for. This is currently done by a hidden row under each persons row that calculates the shift for that person on that day with the given conditions. Also is a 'H' (for holiday), 'RDO' (for requested day off) or 'X' (for unable to work this day) is placed in any of the cells the formular will return value 0. What I am wanting to do is to get rid of all the hidden rows and still have the total calculated in the end column for that person and the day in the bottom row. Is there a way of doing this without having to copy the formula in the hidden cell and adding them in series for each day to achieve the total? I hope this makes sense. Ive attached images to try and help with the explanation.

Thanks in advance

iPad 2, iOS 8.3

Posted on Apr 28, 2015 3:13 AM

Reply

Answer 1

Best reply

t quinn

Level 5

7,042 points

Apr 28, 2015 9:13 PM in response to Gary Pet

Hi Gary,

It makes sense to lose the extra rows. You still need someplace to do the calculations you do there. Rather than extra rows I would have extra columns. May 4 total hours, May 5 total hours, etc.. These could be in a block between the Sunday column and total. By grouping the columns together you would be able to hide/unhide easily.

What do you think?

quinn

Reply

Answer 2

Gary Pet Author

Level 1

0 points

Apr 29, 2015 12:16 AM in response to t quinn

HI Quinn,

Thanks for the advise. That's a really good idea. Changing the totals to coloumns would mean that if the staff change and any point and rows need to me added or deleted it wouldnt be too much hassle to do it. I Think I will make these changes now. Unless anyone can conjure up a sophisticated function that would rid the need for the hidden total columns?

Gary

Reply

Answer 3

t quinn

Level 5

7,042 points

Apr 30, 2015 11:31 PM in response to Gary Pet

Hi Gary,

I believe that it is generally good practice to choose several smaller formulas over large and complex ones. As you noted in your question you could add your daily IF()s to get the result you want but it would be an unwieldy construction. I also suspect that the daily hours for each employee could be useful info.

I hope things are working out for you.

quinn

Reply

Answer 4

Gary Pet Author

Level 1

0 points

Apr 30, 2015 11:31 PM in response to t quinn

Hi Quinn,

I re-worked my table to your suggestion and it works great. We are about to take on a lot more staff so the new table construction will really help me when adding new rows.

THank you very much for your time. You have been very helpful.

Reply

Answer 5

t quinn

Level 5

7,042 points

May 1, 2015 4:21 PM in response to Gary Pet

Hi Gary,

Glad to do it. Sometimes the Numbers way is a little different from the way I first think to do something. It is usually easier not to try to swim aginst the tide.

quinn

Reply