• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

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
0
tezza73
Asked:
tezza73
  • 5
  • 3
1 Solution
 
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now