baleman2
asked on
Excel Spreadsheet Help needed. Nested IF STATEMENT problems.
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
Would like to accomplish the needed tasks with IF STATEMENT, if possible.
C--Users-dan-Desktop-Payout_to_Pool.xlsx
hallo,
please check attached excel if it fits your requirements
Copy-of-C--Users-dan-Desktop-Payout.xlsx
please check attached excel if it fits your requirements
Copy-of-C--Users-dan-Desktop-Payout.xlsx
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect !!
Just what I needed !!
Just what I needed !!
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,T
G2: =VLOOKUP(C2-A2,tblData,3,T
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
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,T
G2: =VLOOKUP(C2-A2,tblData,3,T
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
G2 =VLOOKUP(C2-A2,{0,0.01,"0-
Q2 =MAX(0,E2*M2)
S2 =MIN(0,I2*E2)