Index/Match error when adding Floor function

[swsawyer]

Scenario:
Looking up daily pay rates on an excel table, based on Paygrade and Years of Service.
Problem: Ask Employee #1 “How many years of service do you have?”
If he replies with an even number (e.g. “28 years of service”), then my Index/Match formula works as intended, therefore no issue, all is well.

If he replies with “29 years of service” then I have an issue. Longevity pay increases are based on 2 year increments, so 29 yrs of service are paid at the 28-year rate. When the odd number year in entered into the sheet, an error occurs, as there is no match for the 29 being entered.

My work around was to use reference a new cell, utilizing the function FLOOR, to change the entered Years of Service to an even number, thus being able to find a correct match, which SHOULD result in the correct daily pay rate. However, adding this FLOOR cell reference causes an #NA error.

I have pulled, what little bit remains of my, hair out for over a week, but this one has me stumped. Any help is greatly appreciated.

Based on what I am reading at the bottom of the page, I can't post attachments. And, operating from a government computer, I can't use any of the add-ons intended to use when posting 'small screen shots', so I will attempt to describe my screen shots efforts below:

The "Pay Table" is a group of cells, formatted as a table, where Paygrades are listed down on Column A and Years of Service are listed on corresponding rows, beginning at Year 0 and ending at Year 40.

The following works as intended:

B1 – Drop down selection
B2 – formatted as text, digits entered manually
B3 – Daily pay rate, Index/Match to D1/D2
B4 - # days of work performed
B5 - =B3*B4

B1 – Drop down selection
B2 – formatted as text, digits entered manually
C2 - =Floor(B2,2)
B3 – Daily pay rate, Index/Match to F1/F2
B4 - # days of work performed
B5 - =B3*B4

 

[duggie33]

Hi swsawyer,

I am not certain why the "floor" reference is not working. I have never used it but I have used the MOD function to determine if a value is odd or even. MOD returns the remainder of dividing a specified value by a specified divisor so the remainder of an even number is zero when the divisor is two. For example MOD(28,2) = 0 so 28 is even and MOD(29,2) = 1 so it is odd. You could use this in an if statement to return 28 when the years of service is 29. Similarly, it could return 30 for the years of service being 31.

With A1 being the years of service the formula would be: =IF(MOD(A1,2)=0,A1,A1-1)

It sounds as if the rest of your calculations may work if you implement this in there and it works.

Hope this helps,

Doug

edit: just looked at FLOOR, it seems like it should work equally as good as MOD using significance of 2. let us know if you get either working.

 

[AhoyNC]

Your data in B2 would need to be numeric not text for floor to work.
A work around to try in C2 would be =FLOOR(B2*1,2)