Excel Workout 17 - If Function!

[IlgarZarbaliyev]

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
Please follow the directions given below, which include downloading the Excel worksheet required to perform the challenge tasks. Once you have completed the download, proceed to take the challenge and test your skills.

Task

• 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
Reply to this post with your formula code and solution file. Please be sure to blur or hide your formula code.

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

[QuadriAtharuOlayinka]

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)

[IlgarZarbaliyev]

Hello Quadri,

Thank you for your solution.
Different approach.
Appreciate.

[Tural]

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

Penalty:
=B2*0.03*E2

Challenge #17-TuralMammadov.xlsx (8.3 MB)

[ClaudiaRoxo]

Here is my file

Challenge #17.xlsx (1.7 MB)

[FaridMammadzada]

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)

[IlgarZarbaliyev]

Using Days function is great.
Thank you for your solution.

[Keith]

Hi @IlgarZarbaliyev,

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

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

thanks for the workout
Keith

[munachimarcel]

[details="Summary"]
[spoiler]
=IF(DAYS($D2,$C2)-4 >= 1,DAYS($D2,$C2)-4,0)
=($B2* 0.03*$E2)
[Munachi_Challenge #17.xlsx|attachment](upload://58EJPtcgMR57Wrgz0wBnlcpoiPV.xlsx) (8.3 MB)
[/spoiler]
[/details]

[Rose]

Here is my submission.

Challenge #17.xlsx (8.3 MB)

[homesh_agarwal]

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)

[IlgarZarbaliyev]

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!

[Sujan_Chhetri]

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)

2. 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)

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

=SUM(E2:E35)

4. 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)

[chidinmaamara.eke73]

Answer

[details=“Summary”]
Code used in cell E is =IF(D2>C2+4, D2-(C2+4), “Not Overdue”)
Code used in Cell F is =IFERROR(((B3*3%)*E3), “No Penalty”)

Summary

Copy of Challenge #17.xlsx (8.3 MB)

[steve.waring]

Challenge #17 -Steve Waring.xlsx (8.3 MB)