Using If Function with tick boxes

[Skaapie]

Hi, I am brand new here and I am so glad to find you guys! Here it goes...

I have created a Time Sheet for calculating the work hours of employees. There are various criteria which play a role in how work hours and wages earned are calculated (ex. time, over time, LOA, Travel time, stat holidays etc etc.)

In a certain cell I have placed a tick / check box which the user can tick to indicate that that particular day is a statutory holiday. The tick creates a True / False answer in another Linked Cell.

Based on the True / False result I have a formula which, using the IF function with multiple conditions whether the result is True or False, will calculate the hours worked for Regular, Time and Half and Double Time.

The Formula itself works but when I add the condition based on the check box - example: IF(c37=TRUE,...,if(...,if(...,IF(c37=FALSE,...,if(...,if(... and so on, only the TRUE option works. As soon as the check box is "unticked" I get the result "false".

I hope that all makes sense.

If it helps this is my formula... where C37 is the linked cell for the checkbox.

=IF(C37=TRUE,IF(B10<4.1,B11,IF(B10<8.1,4+(B10-B11),IF(B10<12.1,B10-B11+4,IF(B10>12,8,IF(C37=FALSE,IF(B10<8.1,0,IF(B10>8,IF(B10<12.1,B10-B11,4)))))))))

So, when the check box is ticked, the TRUE argument works fine. But when the check box is not ticked I get a "false" result.

Your assistance will be of HUGE help. In advance... Thank you!!!

If you need more info please ask.

Skaapie

[NBVC]

Try:

=IF(C37=TRUE,IF(B10<4.1,B11,IF(B10<8.1,4+(B10-B11),IF(B10<12.1,B10-B11+4,IF(B10>=12.1,8,0)))),IF(C37=FALSE,IF(B10<8.1,0,IF(B10>8,IF(B10<12.1,B10-B11,4)))))

[Skaapie]

Hmmm, that definitely helps! It did give a result - other than false! I see though, I will have to play a little with my formula to get the correct result now.

I was racking my brain for several hours on that. Just couldn't see the simplicity of adding the correct brackets! Thank you. Thank you. Thank you. Thank you!!!

I will post the final success shortly.

[Skaapie]

NBVC you have been a great help. I am very impressed with how quick you were able to answer me! This is quite an urgent project (for my work place) so your input has been of great value!

I have used you suggestion in another cell and it does seem to work, however, now by applying the same suggestion in another cell which has a few more arguments I am getting a #value! result. I seem to be missing something or doing something wrong. It is exactly the same scenario as before, just a few more arguments. This is the formula... for which I am getting #value!

=IF(C37=TRUE,IF(B10<4.1,0,IF(B10<8.1,B11-B12,IF(B10<9.1,B11-B12+2,IF(B10<10.1,B11-B12+4,IF(B10<11.1,B11-B12+6,IF(B10<12.1,B11-B12+8,IF(B10<13.1,B11-B12+10,IF(B10<14.1,B11-B12+12,IF(B10<15.1,B11-B12+14,IF(B10<16.1,B11-B12+16,IF(B10<17.1,B11-B12+18,IF(B10<18.1,B11-B12+20,IF(B10<19.1,B11-B12+22,IF(B10<20.1,B11-B12+24,IF(B10<21.1,B11-B12+26,IF(B10<22.1,B11-B12+28,IF(B10<23.1,B11-B12+30,IF(B10<24.1,B11-B12+32))))))))))))))))))),IF(C37=FALSE,IF(B10<8.1,0,IF(B10>8,IF(B10<12,0,B10-B11-4))))

[mewingkitty]

Holy aunt Jemima that's a big If statement...

I'm willing to bet if you describe in depth your conditions, that we can come up with a much more effective method of tackling this.

Something like...

Please Login or Register  to view this content.

Didn't really address all your conditions in there but it's a basic representation of how this could work.

[DonkeyOte]

I just looked at your latest and it would appear it could be condensed ?

Please Login or Register  to view this content.

You don't specify what should happen if C37 is true and B10 >= 24.1.
Also your final arguments for FALSE are a tad unclear, ie you say if B10 < 8.1 then 0, if B10 < 12 then 0 ... given the output for both is the same there's no need for the 8.1 test.

[NBVC]

You have too many nested if statements.. Max 7 allowed...

try:

Please Login or Register  to view this content.

[Skaapie]

You guys are making me laugh at myself - it looks like I need to go to Excel school!

I tried the two suggestions from NBVC and DonkeyOte. They did give a value but not the correct answer (close!!!)

Give me a few minutes and I will lay out the situation... I really appreciate you guys already!!!

"Watch this space".

[Skaapie]

I think I uploaded the file. Does anyone see it?

....Ahhh. It looks like it is there.

The issue is in cell B13. The #value! in cell I13 and J13 are due to the #value! in B13.

Just for the record. I am quite new to Excel compared to you guys so please don't laugh at my "elaborate" formulas.[ATTACH][/ATTACH]

[NBVC]

What is the correct answer.. How do we know? What is the formula giving that is close?

[Skaapie]

Good question... sorry about that.

(Scenario is: a worker who actually works on a stat holiday allowing him to receive regular pay + hours worked starting at time and a half up to 4 hours and the balance at double time.)

If it were written out on paper it would look like this...

Note: x1.5 = time and a half; x2 = double time

Total hours 24
Regular hours 8
x1.5 hours: 4 + 4 hours over time due to stat holiday worked = 8
x2 hours: 12 + 20 hours over time due to stat holiday worked = 32

so the answer should be 32

I really hope that makes sense.

Both DonkeyOte's and NBVC formula is giving the answer 12.

[DonkeyOte]

In your example C37 is False not True thus result is B10-B11-4 - 12, if C37 is TRUE then using my approach result is 32 (not checked NBVC's but I'm sure it's likewise)

[NBVC]

Actually, mine gives the same "FALSE" answer of 12, but in my TRUE part I have B11-B12-Lookup(...)

It should be B11-B12+Lookup(...)

Then it will give 32 where C37 is TRUE

Revised formula:

Please Login or Register  to view this content.

oops,,, I also missed the 22.2 parameter... above fixed to give 32 result...

[Skaapie]

We are getting closer - I think.

If you open the file you can check/tick the right box to see its affects on the formula. Depending on the tick two different outcomes will be calculated.

The "written" example below is based on the box being ticked thus working with the "TRUE" result. When it is un-ticked NBVC's formula get's a -30 answer. (This could very well be my faulty equation.)

[DonkeyOte]

As I say, the formula I suggested returns 12 if unchecked, 32 if checked.
I'm heading off line but I'm sure NBVC will resolve for you, use whichever approach you prefer - NBVC's is more flexible should you wish to adjust your increments.

[NBVC]

We are getting closer - I think.

If you open the file you can check/tick the right box to see its affects on the formula. Depending on the tick two different outcomes will be calculated.

The "written" example below is based on the box being ticked thus working with the "TRUE" result. When it is un-ticked NBVC's formula get's a -30 answer. (This could very well be my faulty equation.)

I did explain above, that I had a couple of minor errors in my formula...

... please see my last post for new formula that gives 32 as does DO's

[Skaapie]

NBVC's updated formula looks like it works with the FALSE value in C37 however the answer for TRUE should be 32, I am getting 30.

[Skaapie]

WE have victory!!!!!

Ok, 32 is the answer!

You guys are terrific!

[Skaapie]

I don't know how to say thank you enough! I really do appreciate your help!

NBVC, you formula is a winner!

Question, how do I get rid of the "box" within my cell where I pasted your formula? There seems to be a "frame" or small window in the cell.


Never mind... I got rid of it. Thanks again. Hope to see you around again sometime. "I will be back!"

[NBVC]

Glad to see it got resolved to your satisfaction.

Can you kindly mark your thread as Solved.?

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save