# Excel Cell Formula Help Required..?

I am trying to find a Formula for a cell on a spreadsheet that will calculate a financial penalty for anyone doing more than 60 private miles per week, but do nothing if they are under the 60 miles threshold.

Example:

• H4 = Private miles completed (Imported from another cell on the sheet)
• I4 = Miles above the 60 miles threshold
• J4 = Financial Penalty (Currently £0.15 per mile over 60)

In Summary:

If H4 is equal to or less than 60, I want I4 & J4 to show 0.00 & £0.00

(ie... H4 = 49.00, I4 = 0.00, J4 = £0.00)

If H4 is greater than 60, I want J4 to calculate the additional miles above the 60 miles threshold multiplied by £0.15 per mile.

(ie... H4 = 82.00, I4 = 22.00, J4 = £3.30)

Thanks in advance for any help to a complete excel novice..!!

``````IF (H4 <= 60,0,H4-60)
IF (H4 <= 60,0,I4*0.15)
``````

First line is for I4 and second line is for J4, put a = in front to make it a formula.

IF FUNCTION

Note that this is indeed extremely beginner stuff and easy use case, so a quick Google search would be better.

in I4 =IF(H4>60,H4-60,0) in J4 =I4 x 0.15) You can don't need a more complicated formula as if the value in H4 is less that 60, the value will be 0 x .15 or 0.

What would probably help you get used to an if formula is to enter =IF in a Cell click on the insert formula box highlighted below and let Excel guide you with the formula. A simple "jump start" with spreadsheets.

Yet another way to do this, here including the essence of spreadsheets:

H3 and H4

``````Private miles
82
``````

I3 and I4

``````=CONCAT("Penalty/mile > ";TEXT(K4;"#"))
=IF (H4 <= K4;0;H4-K4)
``````

J3 and J4

``````Penalty £
=I4*L4
``````

K3 and K4

``````Miles threshold
60
``````

L3 and L4

``````=CONCAT("Penalty/mile > ";TEXT(K4;"#"))
0,15
``````

Will give you this display At this stage you may now change not only `82` but also `60` or `0,15` and all other values will adjust. (The exact same will work also in LibreOffice).

Note: I have `,` for decimal separator in my Libreoffice, it might be `.` in your spreadsheet.