Excel Problem using Variables
Morning Gurus
Well it is morning in the UK & on Grand National Day - I fancy Majestic Concorde.
Anyway please would you kindly help me.
I am trying to devise a Cost Spreadsheet based on a Shift Pattern. As you will see from the attached spreadsheet I have achieved the end result but poorly I may add.
Firstly and to avoid any problems - am I correct in assuming that you can name a variable and assign that variable a value? I am assuming you can and would dearly like to use that as Tariffs may vary from here on in.Incidentally the Tariff is the same throughout.
Now to try to explain: The spreadsheet is setup Rows 3 - 368 in daily format and the Shift Pattern is indicated M - Mornings A - Afternoons N - Nights. The hours per shift vary as follows: M = 7.2 hours A = 8.2 hours N = 7.8 hours D = 7.4 hours. Tariff is assumed to 10.5 (But will change).
Columns AH - BH crudely capture the cost/per man/per shift but if you look at the Formula in the cell it uses the VARIABLE declared in Sheet 2 and it is all manually input. I don't like this as it is laborious to populate and errors can occur. Hence Variable. The COLUMN's F - AF lay out the shift pattern and I would like the calculation to emanate from there if possible, so that if I changed the shift pattern by merely editing the A for Afternoons to N for Nights, the calculation would happen automatically.
Also I have staged the calculation COLUMN's AH-BH Capture the DAILY COST then COLUMN's BJ-BP Calculate the COST PER SHIFT then COLUMN's BR-BX Calculate the WEEKLY COST PER SHIFT and then finally COLUMN's BZ-CF calculate the PERIOD COSTS PER SHIFT.
Could it be simpler to end up with the PERIOD COSTS PER SHIFT ?
I tried but could not figure it, so I thought that I would ask you guru's for help please.
Regards
Tezza73 TEST-PATT.xls
Well it is morning in the UK & on Grand National Day - I fancy Majestic Concorde.
Anyway please would you kindly help me.
I am trying to devise a Cost Spreadsheet based on a Shift Pattern. As you will see from the attached spreadsheet I have achieved the end result but poorly I may add.
Firstly and to avoid any problems - am I correct in assuming that you can name a variable and assign that variable a value? I am assuming you can and would dearly like to use that as Tariffs may vary from here on in.Incidentally the Tariff is the same throughout.
Now to try to explain: The spreadsheet is setup Rows 3 - 368 in daily format and the Shift Pattern is indicated M - Mornings A - Afternoons N - Nights. The hours per shift vary as follows: M = 7.2 hours A = 8.2 hours N = 7.8 hours D = 7.4 hours. Tariff is assumed to 10.5 (But will change).
Columns AH - BH crudely capture the cost/per man/per shift but if you look at the Formula in the cell it uses the VARIABLE declared in Sheet 2 and it is all manually input. I don't like this as it is laborious to populate and errors can occur. Hence Variable. The COLUMN's F - AF lay out the shift pattern and I would like the calculation to emanate from there if possible, so that if I changed the shift pattern by merely editing the A for Afternoons to N for Nights, the calculation would happen automatically.
Also I have staged the calculation COLUMN's AH-BH Capture the DAILY COST then COLUMN's BJ-BP Calculate the COST PER SHIFT then COLUMN's BR-BX Calculate the WEEKLY COST PER SHIFT and then finally COLUMN's BZ-CF calculate the PERIOD COSTS PER SHIFT.
Could it be simpler to end up with the PERIOD COSTS PER SHIFT ?
I tried but could not figure it, so I thought that I would ask you guru's for help please.
Regards
Tezza73 TEST-PATT.xls
=INDIRECT(F3)*Tariff
ASKER
Hi expert
Where there is no M A N in the COLUMN's F - AF and the formula you quoted "=INDIRECT(F3)*Tariff", you get an error "#REF", subsequent to that the rest of the spreadsheet goes in error and no end result.
Regards
Tezza73
Where there is no M A N in the COLUMN's F - AF and the formula you quoted "=INDIRECT(F3)*Tariff", you get an error "#REF", subsequent to that the rest of the spreadsheet goes in error and no end result.
Regards
Tezza73
Try this file. I have inserted various error checks and automated a few other mannually entered formulas.
Saqib
Copy-of-TEST-PATT.xls
Saqib
Copy-of-TEST-PATT.xls
ASKER
Hi Saqib
Even more confused now. Hmmm
May I ask you to put the totals in on a Saturday please ? I would do it myself but I cannot figure the fomula.
Each week ends Saturday and the Periods terns are 4 week, 4 week, 5 week repeating three times and the last 5 week turns out to be 6 week becuase it if Financial Year end.
Can you explain the formula please Saqib.
Regards
Tezza73
Even more confused now. Hmmm
May I ask you to put the totals in on a Saturday please ? I would do it myself but I cannot figure the fomula.
Each week ends Saturday and the Periods terns are 4 week, 4 week, 5 week repeating three times and the last 5 week turns out to be 6 week becuase it if Financial Year end.
Can you explain the formula please Saqib.
Regards
Tezza73
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for the explanation. I wish I was as experienced as you gurus in Excel.
Regards
Tezza73
Regards
Tezza73
>>>I wish I was as experienced as you gurus
Hang on to this website. Look at other people's questions. Attempt to solve those that you can. It will be only a matter of time before your wish comes true.
Saqib
Hang on to this website. Look at other people's questions. Attempt to solve those that you can. It will be only a matter of time before your wish comes true.
Saqib
Saqib