Automatically Calculate Cell

[honkin]

Hi
I am trying to put together a racing results sheet and need some assistance with one particular task.

Price are available often as a fractional number or a decimal eg 5/2 In decimal this would be expressed as 3.50, as you are also adding back in the 1 stake amount.

So if it was a calculation for 5/2, it would be =5/2+1 That is just a cumbersome way to have to input and I am wanting to hand the sheet over to someone else and would love to have the result appear in the cell when simply 5/2, or any fractional number is typed. I tried just formatting the column as number with 2 decimals, but it naturally doesn't know to add the 1 to the result

Is there a way to automate this, bearing in mind that new selections are going in daily? So each day, selections for the day are pasted in from another Excel sheet and the following day the results are added. I just want the sheet to constantly calculate a single column each time a fractional number is typed.

I looked at conditional formatting as well, but that way seems to hard code whatever fraction I put in the formula. I simply want it to take 15/8 and make it the result of =15/8+1, which is 2.88

Thanks so much in advance

 

[honkin]

Hey @juddaaaa

That is absolutely brilliant. It appears to work fine.

One tiny thing and it is very small; is there a way for whole numbers to not have 2 decimal places; so 12 rather than 12.00? The person who will eventually be using this file will be inputting fractions, even though some of them will end up being whole numbers; 6/1, 12/1 and so on. The same rule applies where the result would be as if the formula =6/1+1 was typed, but I have not been able to find a format where whole numbers do not have 2 decimal places added.

If I use General, it naturally treats the input as a date, so tried, different cell formats to try and get it to work. It outputs correctly in many of them, but still outputs 2 decimal places for a whole number.

Other than that, it is brilliant

Thanks so much for your time and effort.

Regards

Solved @juddaaaa

I found this code to replace the number format and now whole numbers do not appears as 9.00 and so on

If (.value - Fix(.value)) = 0 Then
        .NumberFormat = "0"
      Else
        .NumberFormat = "0.00"
   End If

Thanks again for your help