• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Nested IF statement issue

E

excel_noobie

New Member
having an issue trying to figure this out.

basically, what I am wanting to write is something that looks at a few date fields, and determine the projected payments on that date, but being able to forecast it in a way that we aren't double dipping between scheduled payment and forecasted payment. does that make sense? I've attached a quick file with a couple different scenarios to help us.

if the payment date is 5/30, I should expect to see it every week before and up to 5/30 and nothing after just so I don't forecast those dollars anymore.
 

Attachments

  • test.xlsx
    11.3 KB · Views: 0
Last edited:
Hi excel_noobie,

Can you give us a picture of what the result should look like? Are we taking the Gross Payment, or Amount Due? Would amount appear only 1 date, or in all dates?
 
so the end result would be this:

for supplier A: I would only expect to see an amount in the 5/25 column because it is due 5/30 and would not fall on 5/18 since it is driven on the Due Date, so the next time I would see it again was on 6/1 if it was not paid yet, since 5/30 is within the week of 5/25-5/31. Hope that helps!

for supplier B: I would only expect to see an amount in the 5/25 column because this has been a payable since 5/7, but each week it is not paid, it would move out & should show up based on (A1) and not the Due Date anymore.

Does that make sense? Sorry I am trying to make sure you understand what I am looking for.

I guess the other daunting piece to this puzzle would be making sure anything that falls after the due date, would still show up in their corresponding columns to be "forecasted"

So does this mean it would be like a multiple criteria formula? Like IF this, then this, if and only if it meets this criteria and that. (blanket statement)
 
Last edited:
Where in the file do we indicate if an item has been paid?

W/o that information, there's no difference between actual/forecast, and our formula can simply be:
=IF($P3<=Q$1+7,$H3,0)

as we don't know when actual pay comes in, nor do we care what actual day it is today.
 
so think of it like this:

on 5/25, you create a report using your aging and disbursements.
your aging will reflect anything that still needs to be paid.
so creating a table using this information that would reflect would require some formulas. I don't care when it's paid, I just care that is paid, right? Because whenever it is paid, does not interfere with what we are doing.

the goal would be that: if I ran a report on 5/25 using this information, I would want to see three things.
1. if I didn't pay the supplier on the "wk of date" that it was originally due, there would need to be a formula to capture that in the 5/25 column now.
2. if it is something that is current, then it should show up on my 5/25 column because it is current and due; which your current logic could work for that after a few tweaks.
3. anything that goes beyond the due date in comparison to the "wk of dates", should be put in their respective columns to be due (aka forecasted) amounts in future. this would be another formula as well.
 
Would it be easier to load another file?

Here is a good example of 3 different scenarios.
Invoice A due 5/30, falls in the 5/25 bucket and only the 5/25 bucket until the due date has passed (formula seems to work ok?)
Invoice B due 5/7, falls in the 5/25 bucket and all previous buckets, but since we are doing this report on 5/25, it is telling us that we have not paid and it's still due so would be included on the 5/25 bucket
Invoice C is due in the future so it should show up somewhere but my formula is not working correctly to make that happen. This would be considered a forecasted payment to be made if I made a summary page.
 

Attachments

  • test.xlsx
    11.8 KB · Views: 0
Last edited:
Hi All
I have been trying to use IF formula to calculate scores based on four columns but the result gives just short of reading one column.Please comment on the IF formula or suggest a new formula to capture the data in all four columns.
Manythanks in advance.
 

Attachments

  • IF formula.xlsm
    19.1 KB · Views: 0
Tashi said:
Hi All
I have been trying to use IF formula to calculate scores based on four columns but the result gives just short of reading one column.Please comment on the IF formula or suggest a new formula to capture the data in all four columns.
Manythanks in advance.
Click to expand...
Hi ,

Please do not post your question in someone else's thread ; please start a new thread of your own , and post your question there.

Narayan
 
Many Thanks Narayan.Works like majic:)) Was too focused on IF formula only...kindly explain this formula line,,,looks altogether new to me :((=IF(C2="","-",IF(NOT(P2),0,P2*25 + Q2*25 + R2*25 + S2*25))

Apologies for starting new thread within here.Newbie here:((

Thanks again :))
 
Tashi said:
Many Thanks Narayan.Works like majic:)) Was too focused on IF formula only...kindly explain this formula line,,,looks altogether new to me :((=IF(C2="","-",IF(NOT(P2),0,P2*25 + Q2*25 + R2*25 + S2*25))

Apologies for starting new thread within here.Newbie here:((

Thanks again :))
Click to expand...

Hi @Tashi,

As @NARAYANK991 sir already asked to start a new thread,

Pls don't post here & follow the forum rules.

What you did is hijacking of one's post.
 
Hi ,

The helper columns are self-explanatory ; each of them will have TRUE if the corresponding column has Yes in it , and FALSE otherwise.

The final formula in column M is checking to see whether the helper column P has TRUE in it , which will mean that the corresponding cell in column C had Yes in it. If column P has FALSE in it , then this over-rides all other column values , and puts the result to 0.

If column P does have TRUE in it , then for each of the other columns E , F and G , we multiply a TRUE / FALSE result by 25 ; TRUE is equivalent to a numeric value of 1 , and FALSE is equivalent to 0. Thus , for each column that has Yes in it , the result will be incremented by 25.

The same logic applies for the formula in column N , where the helper columns V , W and X have been used.

Narayan
 
This is what I have written so far, and it seems to be working!

=IF(AND(Q$1+6>=$P3, $A$1+6>=$P3, $A$1>=Q$1), $H3, IF($P3<Q$1,0,IF($P3<=Q$1+6,$H3,0)))

I just want to make sure my logic behind that makes sense, but it seems that I have made some really big progress if not, solved my own problem.
 
You must log in or register to reply here.
Back
Top