Big Problem, can't get sheet to calc correctly

H

hendrjon

Column D has a formula in it =IF(B15>=34,"70",IF(B15<34,70-C15))
it reports a 70 if someone takes 34 hours off in a row, or subtracts 70 from whats in colum C.

Column C has a formula in it=SUM(A9:A15) but it needs to report a "0" or the sum of the hours of the last 7 days worked in column A.

Column E has a formula in it =SUM(A2:A11) reporting the hours of the last 8 days worked in column A.

Cell A,B, has no formulas.
A is numbers put in for hours worked each day.
B is used to make Column D report a 70 or a calculation. Don't really want a column B involved but seems only way so far to get the 70 to report in column D.

The idea is to have Column C check Column D to see if a 70 is showing, if so then put a "0" in Column C, or Show a total of the hours in the last 7 rows on Column A, if column D is showing the calculation it is supposed to.

If i put a 35 in Column B then Column D shows a 70, If i put a 22 in Column B then Column D shows a total of what's in Column C -70. And that works.
The 35 and 22 mean nothing other than (greater than or equel to 35) or (less than 35).

I need Column C to show "0" if Column D shows a 70, also if Column D is showing a number other than 70 then Column C needs to do the calc of the hours worked for the last 7 rows in Column A and show that total.

my problem is the formula i am trying =IF(D9=70,"0"),IF(D9<70, SUM(A3:A9)), reports a #VALUE! in C when I put a 35 in Column B, when I PUT 22 in column B it reports a #VALUE! in both cells C,D upsetting both Cells, instead of a 0 or a running total just in C.

I am not sure what it needs to do. Formulas need to see other formulas as numbers? Or how to get them from canceling each other out because they both a reporting a result from each other.
heres the layout below:

A B C D E
Hours Hours Not Last 7 days Hours aval Last 8 days
Worked worked hours worked hours worked
10
7
8
12
5.5
0.5
0 22 #VALUE! #VALUE! this row has the new formula i have been trying =IF(D9=70,"0"),IF(D9<70, SUM(A3:A9)) this dosen't work.
0 45 43 70 55
12 55 15 49
11 56 14 46.5
5.5 34.5 35.5 37.5
3 22 32 38 34
2 35 33.5 70 33.5

The real need for this sheet is to calculate the last 7 days of hours worked and show the totals and, the total hours subtracted from 70 is hours left for the next day.
They can't work over 70 hours in a week 7 days.

I need hours worked each day.
I need total hours worked last 7 days to be calculated and hours avalible calculated. (70 minus hours worked last 7 days.)

If they take 34 or mores hours off in a row then it goes back to 70 and then next day it starts over.

So it should look like this:
Hours Hours Not Last 7 days Hours aval Last 8 days
Worked worked hours worked hours worked
10
7
8
12 if I can do this w/o using column B would
5.5 be better.
0.5
0 22 43 27
0 35 0 70 0
12 35 0 70 0
11 22 56 14 46.5
5.5 22 34.5 35.5 37.5
3 22 32 38 34
2 35 0 70 0
5.5 22 39 31 39


Can someone give me a hand on this? it boggleing me.

Thank you tons

If i need to send the sheet via email let me know.

If you need to send email please use
(e-mail address removed)
 
R

RagDyer

It's late and I'm finally leaving the plant.

Your post is too confusing for this late hour, but just a few cursory
observations.

Take the quotes out of your formulas where they enclose *real* numbers.

=IF(B15>=34,"70",IF(B15<34,70-C15))
=IF(D9=70,"0"),IF(D9<70, SUM(A3:A9))

The 70 and the zero (0) are supposed to be numbers, right?
The quotes makes them text!

Also, 70-C15
is *not* the same as
C15-70

Which do you really want?

Have a nice evening.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Column D has a formula in it =IF(B15>=34,"70",IF(B15<34,70-C15))
it reports a 70 if someone takes 34 hours off in a row, or subtracts 70
from whats in colum C.

Column C has a formula in it=SUM(A9:A15) but it needs to report a "0" or the
sum of the hours of the last 7 days worked in column A.

Column E has a formula in it =SUM(A2:A11) reporting the hours of the last 8
days worked in column A.

Cell A,B, has no formulas.
A is numbers put in for hours worked each day.
B is used to make Column D report a 70 or a calculation. Don't really want a
column B involved but seems only way so far to get the 70 to report in
column D.

The idea is to have Column C check Column D to see if a 70 is showing, if so
then put a "0" in Column C, or Show a total of the hours in the last 7 rows
on Column A, if column D is showing the calculation it is supposed to.

If i put a 35 in Column B then Column D shows a 70, If i put a 22 in Column
B then Column D shows a total of what's in Column C -70. And that works.
The 35 and 22 mean nothing other than (greater than or equel to 35) or (less
than 35).

I need Column C to show "0" if Column D shows a 70, also if Column D is
showing a number other than 70 then Column C needs to do the calc of the
hours worked for the last 7 rows in Column A and show that total.

my problem is the formula i am trying =IF(D9=70,"0"),IF(D9<70, SUM(A3:A9)),
reports a #VALUE! in C when I put a 35 in Column B, when I PUT 22 in column
B it reports a #VALUE! in both cells C,D upsetting both Cells, instead of a
0 or a running total just in C.

I am not sure what it needs to do. Formulas need to see other formulas as
numbers? Or how to get them from canceling each other out because they both
a reporting a result from each other.
heres the layout below:

A B C D E
Hours Hours Not Last 7 days Hours aval Last 8 days
Worked worked hours worked hours worked
10
7
8
12
5.5
0.5
0 22 #VALUE! #VALUE! this row has the new formula i have been trying
=IF(D9=70,"0"),IF(D9<70, SUM(A3:A9)) this dosen't work.
0 45 43 70 55
12 55 15 49
11 56 14 46.5
5.5 34.5 35.5 37.5
3 22 32 38 34
2 35 33.5 70 33.5

The real need for this sheet is to calculate the last 7 days of hours
worked and show the totals and, the total hours subtracted from 70 is hours
left for the next day.
They can't work over 70 hours in a week 7 days.

I need hours worked each day.
I need total hours worked last 7 days to be calculated and hours avalible
calculated. (70 minus hours worked last 7 days.)

If they take 34 or mores hours off in a row then it goes back to 70 and then
next day it starts over.

So it should look like this:
Hours Hours Not Last 7 days Hours aval Last 8 days
Worked worked hours worked hours worked
10
7
8
12 if I can do this w/o using column B would
5.5 be better.
0.5
0 22 43 27
0 35 0 70 0
12 35 0 70 0
11 22 56 14 46.5
5.5 22 34.5 35.5 37.5
3 22 32 38 34
2 35 0 70 0
5.5 22 39 31 39


Can someone give me a hand on this? it boggleing me.

Thank you tons

If i need to send the sheet via email let me know.

If you need to send email please use
(e-mail address removed)
 
V

Vaughan

I put your numbers into a spreadsheet, and came close to what you were looking for with these:

C15: =IF(B15>=34,0,SUM(A9:A15))
D15: =IF(B15>=34,70,70-C15)

I didn't see where column E was coming from, but maybe the above will help you to work out how to write the formula for it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top