Help Me I'm Stupid!!!

[ryevick]

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

[Kevin B]

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)

--
Kevin Backmann


"ryevick" wrote:

>
> (This is for a time card) I need to add positive and negative numbers in
> a column... if the value is over 40 I want to display 40 and place the
> balance in the cell below as overtime hours... if it is under 40 just
> display it and nothing below... What is the formula(s)/function(s) for
> this... Please help (pt. 1) Thanx
>
>
> +-------------------------------------------------------------------+
> |Filename: Time_Card.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4040 |
> +-------------------------------------------------------------------+
>
> --
> ryevick
> ------------------------------------------------------------------------
> ryevick's Profile: http://www.excelforum.com/member.php...o&userid=28882
> View this thread: http://www.excelforum.com/showthread...hreadid=486316
>
>

[Leith Ross]

Hello Ryevick,

Make the following changes to your Workheet

P29 =IF(SUM(P24:P28)>40, "40", SUM(P24:P28))
P41 =IF(SUM(P36:P40)>40,"40",SUM(P36:P40))

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!!!

[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

[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

[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

[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

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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!!!
>
>
> --
> ryevick
> ------------------------------------------------------------------------
> ryevick's Profile:
http://www.excelforum.com/member.php...o&userid=28882
> View this thread: http://www.excelforum.com/showthread...hreadid=486316
>

[ryevick]

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