0

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..!!

0
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.

| improve this answer | |
0

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.

enter image description here

| improve this answer | |
0

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

Excel/LibreOffice 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.