Massive 'IF' Function Problem!! VCE SAC!

[steviiiiiiee]

Hey Guys,

I'm doing a Case Study for school on Excel, but our teacher didnt teach us this.

What we have to do is list how many hours the worker has worked or underworked.

For example, for Worker A as Worked 30 hours, but was expected to work 60 hours, he Under Worked 30 hours, so in the 'Under Worked" Column, I would put 30.

I'm using

=SUM(H2-F2)

Just a simple subtraction etc.

Anyway. What I want to do is prevent a negative piece of data being submitted, so if someone over worked, a negative piece of data would go into "Under Worked". I just want it to equal zero, but still be custmizable is the data changes!


Thanks in advance!

 

[repairman615]

Maybe:

if(sum(h2-f2)=0,0,sum(h2-f2))

 

[steviiiiiiee]

Maybe:

if(sum(h2-f2)=0,0,sum(h2-f2))

Sorry does not work.

But I think I worked it out, I changed the Cell Format to.

General;"0"

 

[steviiiiiiee]

Actually No, it does not work. It only shows the data as Zero, but when I times the data with another cell it doesnt act as zero......??!?

 

[braindiesel]

Hi,

First thing, your formula is redundant
=SUM(H2-F2)

Just write =H2-F2.

if H2 = 2 and F2 = 1, your formula says =Sum(2-1) which becomes =sum(1) which is 1.. see the redundancy?

Now to only do the math when greater than 0, how about
assuming
H2 = Expected
f2 = Worked

=if(H2>=F2,h2-f2,0) This will show hours underworked.

In a separate cell, if you want to show overworked

=if(H2<F2,F2-H2,0)
<F2,F2-H2,0)<F2,F2-H2,0)<F2,F2-H2,0)< p>
Does that help?

 

[repairman615]

I see something I may have botched,,

try:

=if(sum(h2-f2)<0,0,sum(h2-f2))

 

[steviiiiiiee]

=IF(H4>=F4,H4-F4,0)

I used For under Worked Hours
and WORKED!!

and for Over Worked Hours I used

=IF(F2>=H2,F2-H2,0)

and it WORKED!!

Thanks you both for your quick replies, if I need anymore help I'll just post in here again.

Once Again thanks A lot!

 

[repairman615]

You are welcome and welcome to the board, if you have any new questions, start a new thread. You will have the benifit of others here with superior brain power who will be more apt to look at a new thread, than here.