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
tezza73Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
In cell AH change N to INDIRECT(F3)

Saqib
0
Saqib Husain, SyedEngineerCommented:
=INDIRECT(F3)*Tariff
0
tezza73Author Commented:
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
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Saqib Husain, SyedEngineerCommented:
Try this file. I have inserted various error checks and automated a few other mannually entered formulas.

Saqib
Copy-of-TEST-PATT.xls
0
tezza73Author Commented:
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
0
Saqib Husain, SyedEngineerCommented:
Hello Tezza73

>>...put the totals in on a Saturday...

Just change the "Fri" in the formula to read "Sat".

>>Each week ends Saturday and the Periods terns are 4 week, 4 week, 5 week....

I am not sure I understand what this means

>>Can you explain the formula...

Do you mean this formula?
=IF(TEXT($E13,"ddd")="Fri",SUM(OFFSET(BJ13,MAX(ROW()-6,13)-ROW(),0,MIN(ROW()-2,7))),"")

(I have selected the formula in row 13 instead of row 3 to simplify the explanation)

This will calculate something if e3 is Fri. In all other cases it will give a blank cell "".

the something is

SUM(OFFSET(BJ13,MAX(ROW()-6,3)-ROW(),0,MIN(ROW()-2,7)))

This is the sum of numbers within the range given by offset
SUM(OFFSET(BJ13,MAX(ROW()-6,3)-ROW(),0,MIN(ROW()-2,7)))

The offset formula can be reduced (in normal cases) to

OFFSET(BJ13,-6,0,7)

which means that
from cell BJ13 go
-6 cells down (meaning 6 cells up) -> cell BJ7
0 cells to the right -> BJ7
and 7 rows deep -> BJ7:BJ13

The actual offset formula used has been modified to accommodate the rows above row 9 where the range would otherwise try to go above row 3, thereby giving wrong results.

Saqib
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tezza73Author Commented:
Thank you very much for the explanation. I wish I was as experienced as you gurus in Excel.
Regards
Tezza73
0
Saqib Husain, SyedEngineerCommented:
>>>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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.