Solved

MS Excel 2013 sheet If statements

Posted on 2014-10-18
2
379 Views
Last Modified: 2014-10-19
I am working on the excel sheet excerise and I am have issues with the following:

Column F: "Amount Due"

If the value is less that $100 I need the amount to show instead of the error message.

Column F: "Action"

I've got this almost working. Again here if the amount is less the $100 and it is not "Paid" a blank cell should appear in the "Action" column

Here is what I am trying to do:

Discount (Column E): Use a nested form of the If function to display 15% if Amount is greater than $300., 10% if Amount is greater than $100

Amount Due (Column F): calculate Amount Due (Amount minus the discount of Amount).

Date Due (Column G): calculate Date Due (Inv. Date plus 30 days).

Status (Column I): Use a nested form of the If function to display “Late” if B3 (system date) is greater than Date Due and Paid is not Yes. Otherwise the cell should appear blank (use 2 double quotes for blank “”). An absolute reference will be required in your formula.

Action (Column J): Use a nested form of the If function to display “Collection” if Status is “Late” and Amount Due is greater than $100.00; “Write Off” if Status is “Late” and Amount Due is less than or equal to $100.00. Otherwise the cell should appear blank (use 2 double quotes for blank “”).
146jrc03.xls
0
Comment
Question by:James Coats
2 Comments
 
LVL 5

Accepted Solution

by:
C. Blaise Mitsutama earned 500 total points
ID: 40389633
SamuelClemmons,

You were very close. With nested IF statements, sometimes trial and error is the only way to work it out.

I've attached an updated file. Let me know if anything is missing.
146jrc03-rev1.xls
0
 

Author Closing Comment

by:James Coats
ID: 40390249
Dear Ms. Mitsutama,

Thank you very kindly for your assistance in this matter. I am just starting my journey concerning excel sheets. Your help has been quite informative.

Sincerely,

Samuel Clemmons
0

Featured Post

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question