Help Me I'm Stupid!!!

[ryevick]

(This is for a time card) I need to add positive and negative numbers i
a column... if the value is over 40 I want to display 40 and place th
balance in the cell below as overtime hours... if it is under 40 jus
display it and nothing below... What is the formula(s)/function(s) fo
this... Please help (pt. 1) Thanx :eek

+-------------------------------------------------------------------
|Filename: Time_Card.zip
|Download: http://www.excelforum.com/attachment.php?postid=4040
+-------------------------------------------------------------------

 

[Guest]

I was unable to get the posted workbook, but here's a possible solution.

Column A Column B
Column C
Total Hours Regular Hours Overtime Hours
45.6 =IF(A2>40,40,A2) =IF(A2>40,A2-40,0)

 

[Leith Ross]

Hello Ryevick,

Make the following changes to your Workheet

P29 =IF(SUM(P2428)>40, "40", SUM(P2428))
P41 =IF(SUM(P3640)>40,"40",SUM(P3640))

Sincerely,
Leith Ross

 

[ryevick]

It still doesn't work!@%#...

This sheet well be tied into a large workbook that pulls data from this
sheet and does quite a lot in other sheets from what it gets here... but
I gotta get this going for the whole thing to work... by the way... I
know it's a pain, but I'm sure it can be done, I'm just new to
Excel...


-_What_I_need_in_the_attached_worksheet_is_exactly_this_:_

1. Row I29 through P29 to give totals for the hours listed above
them (don't forget the negative values)... P29 results should show no
more than 40... if over 40 place the difference in P30 (overtime).

2. P45 also needs to show no more than 80 with any difference
posted in P46.


3. No errors showing in the sheet (they annoy me even if the thing
works).-


The worksheet is the same for week 1 and 2, so I can carry the formulas
over to the second week unless you're just smart and bored!

I shall publicly crown you the Excel King if you can pull this off!
Thanx!!!


+-------------------------------------------------------------------+
|Filename: Time_Card.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4042 |
+-------------------------------------------------------------------+

 

[Leith Ross]

Hello Ryevick,

Rather than post all the cell formula changes and have you copy them
back into your worksheet, I have uploaded a corrected version.

Sincerely,
Leith Ross


+-------------------------------------------------------------------+
|Filename: Time_Card1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4043 |
+-------------------------------------------------------------------+

 

[ryevick]

Leith, test the file you made and you'll see that it doesn't do as I was
asking... It doesn't... I need the overtime columns functioning...

 

[ryevick]

Leith, test the file you made and you'll see that it doesn't do as I was
asking... I need the overtime columns functioning...

 

[Leith Ross]

Hello Ryevick,

I ran your workbook on my system after makiing the corrections. I
experienced no problems under Excel 2000. You need to be precise about
what isn't working or rather the way you want it to work. Saying it
doesn't work when it does isn't very constructive. Give me an example
or two of the problems and I will be glad to help to you.

Sincerely,
Leith Ross

 

[ryevick]

I am on Excel 2003 (don't guess that matters?) and the problem that I'm
seeing is:

Type 22 into I24 (using the file you uploaded)... your total (for that
day) goes to 53 in P24 (good), P29 shows 40 (which is good) but P30
shows 0 and should show the difference between the sum of the weekly
totals (P24 through P28) and P29 (40) which would be overtime and P45
shows (28).

 

[Leith Ross]

Hello Ryevick,

Thank you for explaining the problem. My apologies. I did make an
oversight in the code. This will get you up and running.

Sincerely,
Leith Ross


+-------------------------------------------------------------------+
|Filename: Time_Card1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4045 |
+-------------------------------------------------------------------+

 

[ryevick]

Thank you... a few more adjustments needed...

(using the file you uploaded) P24 through P30 is correct! P25 & P27 may
need to be adjusted as a normal sum formula and values entered into I25
through O25 & values entered into I27 through O27 need to be converted
to negative values "In Column"... If you look at I27 & I29 you'll see
what I mean... I29 is summing I27 as a postive number. Lastly (I
hope/think) P45 should be showing 68 (I would test it with values over
80 as well to make sure that value never goes over 80 and carries the
differnce to the cell below.

Thanx!

 

[Leith Ross]

Hello Ryevick,

Third time's the charm. I made the modifications you requested and
tested the worksheet. If you need anything else, let me know. The
updated workbook is attached.

Sincerely,
Leith Ross


+-------------------------------------------------------------------+
|Filename: Time_Card1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4046 |
+-------------------------------------------------------------------+

 

[ryevick]

I dub the Sir Leith Ross, King of Excel!!!

It's nice to know there's people that's willing to help others resolve
their problems... I try to do the same.

I like to learn from working formulas and such to get a clue of how to
write my own stuff... two questions... 1) How did you get the negative
number values in column? If I select the cell I don't see a formula
inside it. 2) P25 for example has an error flag next to it (which could
be ignored) but the form seems to be OK... why is that?

Thanx!

 

[Leith Ross]

Hello Ryevick,

What type of error are getting at P25? There are no errors showing in
my copy. In answer to the question of the negative numbers this is what
I did. Since I couldn't use the cell's formula to convert the number
into a negative value, I used a couple slight of hand tricks. Magicians
refer to these 2 techiniques as misdirection and ditching. First the
number was formatted using a Custom number format to make it red, like
a negative value (misdirect). The second part is the Column Sum formula
doesn't include the Luch times (ditch). Let me know about the error.

Sincerely,
Leith Ross

 

[ryevick]

I typed this before I pulled up the forum and saw your reply... instead
of retyping I'll just post it as is...



-In cells P25, P27, P37 & P39 it says "inconsistent formula"... I hate
to say it but I see something that isn't working, but have an idea for
a better way to do it anyway if you can do the formula... I29 through
O29 & I41 through O41... are not showing correct sums for their
respective columns (add a value in a "lunch" cell and you'll see it
doesn't change the toal of the column)... If we make P25, P27, P37 &
P39 just show sum and not convert the sum to negative values and then
convert any input into the cells I29 through O29 & I41 through O41 as
negative value inside each cell that should do it... (ex. I25... input
8... and hit enter... the formula changes the input to a negative
value... -8) This will sum correctly in the P column and the rows as
well...

Thanx-

 

[Leith Ross]

Hello Ryevick,

I made the new changes you wanted. As for the error, you should be able
to turn off the "inconsistant function" error checking. That "feature"
started with Excel 2002. Since I am running 2000, I don't know to
disable it. Your Excel help should have that info. New workbook
attached.

Sincerely,
Leith Ross


+-------------------------------------------------------------------+
|Filename: Time_Card1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4049 |
+-------------------------------------------------------------------+

 

[ryevick]

I only have a second but it looks like its working fine... How did you
write the formula to get the cells (ex. I25) to convert their input to
a negative value? I've looked everywhere for that...

Thanx!!!

 

[Bob Phillips]

If you are not going to share the solution with the group, can you have the
conversation offline?

 

[ryevick]

Well Bob as soon as I get the info I need I will!