?
Solved

MS Excel 2013 sheet If statements

Posted on 2014-10-18
2
Medium Priority
?
399 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 5

Accepted Solution

by:
C. Blaise Mitsutama earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

762 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