Solved

MS Excel 2013 sheet If statements

Posted on 2014-10-18
2
359 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 4

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Microsoft Office 2010 is Microsoft's latest edition to their office productivity collection. It "promises to include rich and powerful new ways to deliver your best work". So to start off, I have to say that I'm slightly disappointed... That bei…
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 and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now