Solved

Excel Spreadsheet Help needed.  Nested IF STATEMENT problems.

Posted on 2016-08-24
6
62 Views
Last Modified: 2016-08-25
Spreadsheet is attached with needed raw data already shown.  Notes concerning formulas are in a text box on the spreadsheet.

Would like to accomplish the needed tasks with IF STATEMENT, if possible.
C--Users-dan-Desktop-Payout_to_Pool.xlsx
0
Comment
Question by:baleman2
[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
6 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41769738
E2 =VLOOKUP(C2-A2,{0,0.01,"0-14 days = 1%";14,0.005,"15-29 days = 1/2 of 1%";29,-0.5,"50% of GS & Commission Deduct";59,-1,"100% of GS & Commission Deduct"},2)

G2 =VLOOKUP(C2-A2,{0,0.01,"0-14 days = 1%";14,0.005,"15-29 days = 1/2 of 1%";29,-0.5,"50% of GS & Commission Deduct";59,-1,"100% of GS & Commission Deduct"},2)

Q2 =MAX(0,E2*M2)

S2 =MIN(0,I2*E2)
0
 
LVL 19

Expert Comment

by:helpfinder
ID: 41769742
hallo,
please check attached excel if it fits your requirements
Copy-of-C--Users-dan-Desktop-Payout.xlsx
0
 

Author Comment

by:baleman2
ID: 41770064
To Helpfinder:

Everything seems to work perfectly except the formula in Column S.  

In the event that our General Manager or CFO reviews the dates and decides to manually change a date (which does happen occasionally), the Column S formula does not populate with data.

For instance, change the C2 date to 07/13/2016.  This puts the date range in the "over 60 days" category.  

E2 and G2 changed correctly and display the correct output.  I would have expected Q2's data to disappear - and it did.  However, S2 should have displayed the 17,220, but did not.
-----------------------------------------------------------------------
Change the date in C5 to 05/02/2016.  This puts the date range in the "less than 15 days" category.

E2 and G2 change correctly and display the correct output.  Q2 displays correct output and S2 is no longer populated with data - which would be correct.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 19

Accepted Solution

by:
helpfinder earned 500 total points
ID: 41770228
OK, I did not see your last d) point because it was hidden in the text box. I updated the formula in S column. Please test it and let me know
Copy-of-C--Users-dan-Desktop-Payout.xlsx
0
 

Author Closing Comment

by:baleman2
ID: 41770282
Perfect !!

Just what I needed !!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41770956
baleman2,

Even though you've selected a solution, I'm submitting an alternate example workbook that greatly simplifies all your data requirements AND gives you more flexibility in updating the rules in the future should you need.

First, it uses a master data table (tblData) that categorizes your Comm Multiple and Phrasing by aging amounts.  This is on a second sheet (which can be hidden, if desired).

That table is referenced by two VLOOKUP functions on your main sheet, both returning the same results you requested but in much simpler form:
E2: =VLOOKUP(C2-A2,tblData,2,TRUE)
G2: =VLOOKUP(C2-A2,tblData,3,TRUE)

They are basically looking for the closest matching category in the "Aging" column to the difference between the "Paperwork Delivered" and "Date Sold" dates.  

I won't go into a lesson on VLOOKUP, but it's worth learning.  Here is a very good online article from Microsoft that will help:  VLOOKUP function

Second, since your Comm Multiple is actually a component of the "to Pool" and "to Qtr. Payout" values, why not actually use them in the formulas?

Q2: =IF(C2-A2<30,M2*E2,"")
S2: =IF(C2-A2>=30,I2*E2,"")

You can see how much simpler this method is compared to checking the phrasing value.  All these elements are related, and since you have the multiples in the data, it's better to go ahead and use them rather than manually restate them in another formula.

I've attached an example workbook with all this.  

Regards,
=Glenn
EE-Users-dan-Desktop-Payout_to_Pool.xlsx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

734 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