Need help with this formula? Way above my skill level.

[68rs327]

I need help with this formula, it's above my skill level. I've worked on this for hours....

[/IMG]

 

[Gerald Higgins]

OK this is probably do-able but there's lots of things going on here, let's try to rationalise it a bit.

1) Is the rate of $14.80 held in a cell somewhere ? If yes, which cell ? We can assume cell A1 if you like.
2) Is the number of hours per shift held in a cell somewhere ? If yes, which cell ? Again, we can assume cell A2 if you like.
3) It might be possible to create a formula which takes into account how complete the circles in column D are, but it would be MUCH MUCH easier if we could do this instead based on some value, such as 100%, or 50% or whatever, entered in column D.
4) You've referred to columns A and B, but they are not visible in your screenshot. What's in them ?
5) Some dates appear twice - does that mean anything significant that we need to think about ?

 

[68rs327]

1) Is the rate of $14.80 held in a cell somewhere ? If yes, which cell ? We can assume cell A1 if you like ​

I don't have $14.80 in a cell it just the hourly rate.

2) Is the number of hours per shift held in a cell somewhere ? If yes, which cell ? Again, we can assume cell A2 if you like. ​

I don't have the hours per shift any cells. It just 8 hrs. per shift.

3) It might be possible to create a formula which takes into account how complete the circles in column D are, but it would be MUCH MUCH easier if we could do this instead based on some value, such as 100%, or 50% or whatever, entered in column D. ​

The circles are filled in when you type in 25 then 25% of the circle is filled. When 100 is typed in then 100% of the circled is filled in black.

4) You've referred to columns A and B, but they are not visible in your screenshot. What's in them ?​

Cells A and B have no effect on the calculation. Cell A is the action taken to reduce labor and cell B show the name of the person responsible for the action.

5) Some dates appear twice - does that mean anything significant that we need to think about ? ​

Dates appearing twice don't mean anything. It only means actions were taken on the same date to reduce manpower.

I could email you the spreadsheet it that would help. If you'd like, you can private message me your email address or I already sent you my email address. This is a very complicated formula.
Thank you for your help.

OK this is probably do-able but there's lots of things going on here, let's try to rationalise it a bit.

1) Is the rate of $14.80 held in a cell somewhere ? If yes, which cell ? We can assume cell A1 if you like.
2) Is the number of hours per shift held in a cell somewhere ? If yes, which cell ? Again, we can assume cell A2 if you like.
3) It might be possible to create a formula which takes into account how complete the circles in column D are, but it would be MUCH MUCH easier if we could do this instead based on some value, such as 100%, or 50% or whatever, entered in column D.
4) You've referred to columns A and B, but they are not visible in your screenshot. What's in them ?
5) Some dates appear twice - does that mean anything significant that we need to think about ?

 

[Gerald Higgins]

1) Is the rate of $14.80 held in a cell somewhere ? If yes, which cell ? We can assume cell A1 if you like
​

I don't have $14.80 in a cell it just the hourly rate.

Well you can do it that way if you like, but if in a few weeks time the rate changes to $14.70 or $14.90, you will probably find it easier to update the formulas, if this value is stored in a cell somewhere, rather than hard coded into the formula.
Let's assume you store it in cell A1.

2) Is the number of hours per shift held in a cell somewhere ? If yes, which cell ? Again, we can assume cell A2 if you like.
​

I don't have the hours per shift any cells. It just 8 hrs. per shift.

Well you can do it that way if you like, but if in a few weeks time hours per shift changes to 7 or 9, you will probably find it easier to update the formulas, if this value is stored in a cell somewhere, rather than hard coded into the formula.
Let's assume you store it in cell A2.

3) It might be possible to create a formula which takes into account how complete the circles in column D are, but it would be MUCH MUCH easier if we could do this instead based on some value, such as 100%, or 50% or whatever, entered in column D.
​

The circles are filled in when you type in 25 then 25% of the circle is filled. When 100 is typed in then 100% of the circled is filled in black.

OK understood, so where are you typing in those values ?

4) You've referred to columns A and B, but they are not visible in your screenshot. What's in them ?
​

Cells A and B have no effect on the calculation. Cell A is the action taken to reduce labor and cell B show the name of the person responsible for the action.

OK, but you said you were looking for a formula such as

=SUMIF(A:A......B:B)

I took that to mean that A:A and B:B were relevant. If they are not, fine, I'll ignore them.

5) Some dates appear twice - does that mean anything significant that we need to think about ?
​

Dates appearing twice don't mean anything. It only means actions were taken on the same date to reduce manpower.

OK fine.

I could email you the spreadsheet it that would help. If you'd like, you can private message me your email address or I already sent you my email address. This is a very complicated formula.
Thank you for your help.

Please don't pm me anything to do with this thread, I'd prefer to deal with it all in public, thanks.

 

[68rs327]

Thanks......

 

[Gerald Higgins]

OK . . . waiting for a response from you to post #4 ,

...where are you typing in those values ?