Index cell by decimal portion

[ssalazarjr]

Hi,

Is there a way to index a cell by the decimal portion of the value?

I have a column of numbers:

119.05
2479.04
3145.03
729.02
592.01

I need to be able to reference the entire value by the decimal portion.

Any ideas?

 

[Joe4]

Not quite sure if this is what you are looking for, but you can return the decimal portion of each with the following formula:

=A1-INT(A1)

Then you should be able to use this calculdated field as your index.

 

[ssalazarjr]

I'm trying to find the cell by using the decimal portion as my search value.

Let me explain a little more. My managers make bank deposits. Previously my deposits were rounded down to the nearest dollar and the change would go to the next day opening drawer.

Example would be:
$119.00
$2479.00
$3145.00
$729.00
$592.00

The problem was that it was very time comsuming to reconcile the bank deposit to by POS system. I had to match each one manually. So I figured out a way to determine what day the deposit belonged to. I'm having the managers deposit coin to match the day of the month.

So now my deposits look like this:
$119.05 Sales for the 5th
$2479.04 Sales for the 4th
$3145.03 Sales for the 3rd
$729.02 Sales for the 2nd
$592.01 Sales for the 1st

Now I'm trying to match my POS system to my bank statement. So I need a way to either MATCH, INDEX, VLOOKUP or some way to identify that the deposit for the 2nd is $729.02 by finding it with the $.02.

 

[Joe4]

If you put the formula I gave you to the left of the actual value, i.e.

0.05 $119.05
0.04 $2479.04
0.03 $3145.03
0.02 $729.02
0.01 $592.01

then you could use a VLOOKUP function on this range to return the value you want (matching on the first column and returning the value from the second).

 

[ssalazarjr]

Any way to lookup with out a helper column?

 

[Joe4]

If your values are entered as Text instead of Numbers, it seems to work.

For example, say that your range is cell A2:A9. If you want to return the non-decimal portion of the entry for the 4th month, this formula seems to work:

=ROUND(INDEX(A2:A9,MATCH("*.04",A2:A9,0)),0)