Change in Pay.

[Helix242]

Hi,

Hoping someone can help me. I have a tracker with a list of dates through the year in Column A and in the next 5 Columns the hours are entered for the hours each member of staff works and these are calculated with the hourly rate in the final Column (see below based on one person)

20/09/2017 7 £8.20 £57.40
21/09/2017 7.5 £19.20 £144
22/09/2017 15 £15.00 £225

However I want to be able to input the hourly rate in on cell for each person, and have the ability to amend this one cell to a new hourly rate if required, but for all the previous dates remain with whatever rate was input.

Is this possible??

Thanks in advance!

Mark

 

[Helix242]

Hi,

Okay, I have got the formula to work, however when I had my previous COMPLICATED formula running, it didn't actually pick up the previous days cells as values, only the formula which meant I couldn't calculate the income. When you checked the previous days, did this work?

Thanks

 

[istiasztalos]

No it will not work due to the circular reference. It only calculates on the given day.

Cells that reference directly or indirectly themselves display a value but don't actually contain it. When that cell is referenced by any formula anywhere in the file it will only read a 0; and that goes on in a chain for further cells as they reference each other. (This means you can't calculate a monthly sum for example)

 

[Helix242]

Okay, so this is what I have been looking for. I need the hourly rate to be entered in that dat, but remain in that day and so on for the next corresponding days throughout the year, but again with only 1 hourly source input.

So are you saying your formula just won't work?

 

[dv8123456789]

Hi - I'm looking to do something very similar. I have an assignment list which details people and effective dates\rates. There are multiple people and one person may have more than one line if their rate changes mid year. I then have timesheets and I want to be able to look up for a particular person what the rate should be so I can calculate the cost. I have seen this done in access where we take the largest (max) effective date that is less than the date worked, but I'm struggling to get my head around how this can be done in excel. I'm thinking perhaps sumifs of the rate if the person =person and the effective date is less than the date worked, but that could apply to multiple rate lines so I need to narrow it down further. Any ideas?

 

[istiasztalos]

Hiya,

It should work to show the daily total. Nothing else... once the day is over and the cell starts to reference itself it will stay frozen, but the major downside of self-references is that you cannot make further calculations (like summing up past days)

As others noted on your other thread with same question, the only viable solution to my knowledge - without VBA - would be to use a rate lookup table, you could have one single rate table for all the staff members and still show the only one and current rate for them on that other sheet where you need it. (the rate table can be hidden somewhere...)

 

[istiasztalos]

Hiya,

Please check my original answer to this thread, if you have a table with rates and their effective start date, a range VLOOKUP can work, if not, you may need to open a new thread. Thanks !

Hi - I'm looking to do something very similar. I have an assignment list which details people and effective dates\rates. There are multiple people and one person may have more than one line if their rate changes mid year. I then have timesheets and I want to be able to look up for a particular person what the rate should be so I can calculate the cost. I have seen this done in access where we take the largest (max) effective date that is less than the date worked, but I'm struggling to get my head around how this can be done in excel. I'm thinking perhaps sumifs of the rate if the person =person and the effective date is less than the date worked, but that could apply to multiple rate lines so I need to narrow it down further. Any ideas?

 

[Helix242]

Hi istiasztalos,

Thanks for all your help, it looks like I'm sorted.

Thanks again