SOLVED! Multiple IF Function In A Single Formula

[serdal22]

Dear Friends,

This is my first post, but it won’t be the last. Sorry for the inconvenience ;-) patience is needed 

I am trying to complete my “Wage Calculator” program. I am done by %95. The only part remains is the “Sea Time” calculation. I searched to see if similar topics were already discussed but I couldn’t see anything through search engine, and decided to post this question.

I am using MS Office 2003. Now my sea time . . . I work several shifts here at Seabus. Some shifts are 4 hours, some are 10 hours, and 10.5 hours. Some shifts are 7 to 8 hours, as well.

My sea time I am gaining when I work (I work 1 week on, 1 week off) is particularly important when it comes to keep my license (Marine ticket) up to date (Continuance Of Proficiency) . I have to work at least 1 year every 5 year period, then I go to harbour master (Transport Canada) to extend it for another 5 years as per “Sea Time” I have gained for the current 5 year period.

Now, the sea time calculation. As per the collective agreement (Between the union and the company), here are the break times we are entitled to take as per the length of our shifts: (Please note that when I am on my break I step out of the Seabus and go to land (Terminal) and that break time is not counted as sea time because I am not on the Seabus).

a – No break for the 4 hour shift; so 4 – 0 = 4 hours of sea time.
b – 3 breaks for the shifts of 10 hours or more; so 10 – 1.5 = 8.5 hours of sea time. (Please note that each break is 0.5 hour).
c – 2 breaks for the shifts more than 7.5 hours but less than 10 hours; so 7.5 – 1 = 6.5 hours of sea time.
d – 1 break for the shifts of 7.5 hours or less but more than 4 hours; so 4.5 – 0.5 = 4 hours of sea time.

As seen in the examples, I am trying to calculate the sea time hours by entering into related cells the length of my shifts. Here is the formula I have used:

=IF($M2>=10,$M2-1.5,IF(7.5<$M2<10,$M2-1,IF(4<$M2<=7.5,$M2-0.5,IF($M2<=4,4))))

The formula works for 4 hours, for 10 and 10.5 hours. For some reason, when I enter 5 (hours) on the column L it gives “False” error.
Basically what I am trying to do is to use multiple IF functions in a single formula. If you could help me with this, I will be grateful to you.

I am still amateur and love Excel a lot for being able to make calculators and programs. I am also working on my Ship Cargo Loading and Stability Program as well. I will post some harder questions regarding to my that program later.

I have (Tried) to upload my wage calculator. I am working on Sept 2010 sheet. When you click on L2 down to L8 cells, you'll see what I mean.

Very Best Regards . . .

Capt. Serdal
CDN Master Mariner, North Vancouver, Canada

[zbor]

You can not use 4<$M7<=7,5 as a syntax in excel (at least not for what you trying to do).

[zbor]

Here, try this:

=MAX(4,M2-LOOKUP(M2,{0,7.5,10},{0.5,1,1.5}))

[zbor]

If you want to use IF function then you should think on this way:

=IF(M2<=4,4,IF(M2<=7.5,M2-0.5,IF(M2<10,M2-1,M2-1.5)))

IF it's less then 4 use 4
IF it's less then 7.5 use M2-0.5

Note that you don't need to check is it bigger then 4 because you already check it in first condition. If it's less then 4 it will take first IF condition, so it MUST be greater then 4 if it's checking second one.

IF it's less then 10 zse M2-1

Same as above, no need to check again is it bigger then 7.5

etc

IF none of prevoius conditions is met then use last M2-1.5

[serdal22]

Dear Friends,

Thank you so much for your very prompt reply, and for the precious information you have provided for me. Tomorrow at work during my breaks, I will read your replies again and try to apply to my formulas, and will let you know how it goes.

Very Best Regards . . .

Capt. Serdal
CDN Master Mariner, North Vancouver, Canada

[serdal22]

Dear Friends,

All right, I applied this formula: =IF(M2<=4,4,IF(M2<=7.5,M2-0.5,IF(M2<10,M2-1,M2-1.5)))
And the entire rows worked like charm. However, when I entered 3 (Hours), the actual seatime (On board Seabus) was still calculated as 4 (Hours). So, I did a small change in the formula as shown here:
=IF(M2<=4,M2,IF(M2<=7.5,M2-0.5,IF(M2<10,M2-1,M2-1.5))) In the beginning, instead of =IF(M2<=4,4... I entered =IF(M2<=4,M2..... And I tried by entering 3 hours and the result was 3 hours.

Please try and see if there is anything wrong in my formula.

Thank you so much for your precious helps and time.

I am starting to work on my Ship Loading and Stability program, and if I need any help, I would like to ask you, again, please.

Very Best Regards . . .

Capt. Serdal
CDN Master Mariner, North Vancouver, Canada

[nimrod]

Here's another solution: With this solution you have Pairs of value in the format #,#; i.e. 4.01,1 ; The first number indicates the start of a range and the second number indicates the "Break Value" of that range.

=VLOOKUP(M2,{0,0;4.01,1;7.51,2;10.01,3},2,TRUE)

[serdal22]

Dear Nimrod,

Yes, I also tested your formula and works perfectly!

Thank you so much again to everybody for your precious helps! You make our lives a lot easier, and I am very much grateful to all of you . . .

Very Best Regards . . .

Capt. Serdal
CDN Master Mariner, North Vancouver, Canada

[nimrod]

Glad I could help a fellow BC'er ;-) Cheers Captian.