# Excel Workout 17 - If Function!

Welcome to Excel Workout #17!
Difficulty Level:

This week’s challenge is designed to test your knowledge on If Function.

If Function
The IF function in Excel is a logical function that allows you to perform different calculations or actions based on a specified condition. It evaluates a condition and returns one value if the condition is true, and another value if the condition is false.

Goals

• In column E cells, determine the number of days by
which the payment exceeds the due date. A payment is
classified as overdue if it is paid more than 4 days after
the date of receipt.

• In column F cells, compute the penalty amount - which is
equal to 3% of the order amount - for every day the
payment is delayed.

• In column I cells, calculate the total number of days the
payment is overdue and the corresponding fine amount
based on the table. Compare the calculated results with
the corresponding values in column K.

Submission

Period
This workout will be released on Monday May 22, 2023 , and the author’s solution will be posted on Sunday May 28, 2023 .

Challenge #17.xlsx (8.3 MB)

Good luck,

Ilgar Zarbaliyev

1 Like

This is quite straight forward

Due Date= Receipt Date + 4

Due Date= C2 + 4

Overdue Days=
IF( Payment_Date>Due_Date, Payment_Date-Due_Date,0)

Or

`=IF(D2>(C2+4),D2-(C2+4),0)`
Or

=IF(D2-(C2+4)<0,0, D2-(C2+4))

Penalty= =3% * Order_amount * Overdue_days

=3% * B2 * E2

Quadri Atharu Challenge #17.xlsx (8.3 MB)

1 Like

Different approach.
Appreciate.

Overdue days:
`=IF(D2-C2>4,(D2-C2)-4,0)`

Penalty:
`=B2*0.03*E2`

1 Like

Here is my file

Challenge #17.xlsx (1.7 MB)

1 Like

Challenge#17.xlsx (8.3 MB)

Overdue (days)

``````=IF(DAYS(D2;C2)>4;DAYS(D2;C2)-4;0)
``````

Penalty, \$

``````=E2*IF(E2>=1;B2*3/100)
``````
2 Likes

Using Days function is great.

1 Like

Here is my solution to the workout #17.

OverDue (Days)

= if((Receipt Date +4)< Payment Date, (((Receipt Date +4)-Payment Date) *-1), 0)

NOte Multiply by -1 make the days positive number)

=IF((C2+4)<D2,(((C2+4)-D2)*-1),0)

Penalty \$
=IF(E2>0,ROUND((0.03B2E2),2),0)
=If(Overdue days > 0, (Round((.03*Amount \$ * Overdue(days),2), 0)
Round to 2 decimals

Excel Workout Challenge #17 Keith Smith.xlsx (8.3 MB)

thanks for the workout
Keith

1 Like
``````

[details="Summary"]
[spoiler]
=IF(DAYS(\$D2,\$C2)-4 >= 1,DAYS(\$D2,\$C2)-4,0)
=(\$B2* 0.03*\$E2)
[/spoiler]
[/details]

``````
1 Like

Here is my submission.

Challenge #17.xlsx (8.3 MB)

2 Likes

Calculating Overdue Days in E2:

`=IF(D2-C2>4, D2-C2,0)`

Calculating Penalty Amt. (\$) in F2

`=IF(E2>0, B2*3%*E2,"")`

Total Overdue Days:

`=SUM(E2:E35)`

Total Penalty (\$):

`=SUM(F2:F35)`

Challenge#17_Homesh Agarwal.xlsb (19.7 KB)

2 Likes

Thank you for participating in the Excel Challenge related to IF Function! I hope you found this challenge to be a fun and engaging way to improve your Excel skills and learn more about how to work with IF Function in Excel.

Here is my solution to Workout #17:

• To find Overdue (days):
=IF(D2-C2<=4,0,(D2-C2)-4)

• To find Penalty, \$:
=E2B23%

Challenge #17 with Solution.xlsx (8.3 MB)

Once again, thank you for participating in this Excel Challenge, and I look forward to seeing you in future challenges and learning opportunities!

1 Like
1. Overdue(days) calculation formula logic:
IF Payment Date > (Receipt Date + 4)
THEN Payment Date - (Receipt Date + 4)
ELSE 0

=IF(D2>(C2+4),D2-(C2+4),0)

1. Penalty,\$ calculation formula logic:
IF Overdue(days) IS NOT ZERO
THEN 3% * Amount * Overdue(days)
ELSE consider the penalty as zero

=IF(E2<>0,0.03B2E2,0)

1. Total Overdue(days) [column I] calculation:
SUM of all positives in the Overdue(days) [column E]

=SUM(E2:E35)

1. Total Penalty,\$ [column I] calculation:
SUM of all penalty, \$ in the [column F]

= SUM(E2:E35)

My Excel Workout File: Challenge #17.xlsx (8.3 MB)

1 Like