Complex If Statement

Ok, I have a fairly complex (and probably unsophisticated) if then statement.

It worked on one row, but when I copied it down, it didn't seem didn't work anymore.

I'm confused by it, it's fairly long, and I'm not sure where the problem is... I've included an example, but I have to copy this down for 1000 rows, so I need that functionality.

Thanks!

i just realized I had it saved as a 2007 file, and was going to convert it back to 2003 for everyone, however it said my formulas were too long to do that... is there a cleaner way to accomplish the same task?

sorry, it seems as if thethe first row of it works, and the rest when copied down, doesn't.

there are 3 seperate areas, and they take an order of importance as follows:

1. Payment Plan Schedule
2. Promised Pay Date
3. Invoice Due Date

Each one of these is basically an escalation in the accounts payable procedure. There are 4 "Payment Plan" dates that can be entered, as well as a monetary amount. These should be reflected in the weeks on the right. The "Promised Pay" date should do the same, and the Invoice Due Date, last.

So, if there is nothing for a date or amount in the payment plan, it would revert to the promised pay date, and if nothing in that then to the invoice date.

*** first thing it would do is check to see if it has been "x" for paid, of course.

I'm assuming so also (for the conversion of 2007 - 2003), There are 6 cells to check for data against changing cells to check date.and it needs to roll forward by using a copy past function.

I do think that the top row is right, but when I copy and past it across, it doesn't work.... And, I'm not sure if anyone here has a trick to try and edit long formulas easily?

I tried word, but without the colored groupings that was hard.... I'm sure i screwed up something simple like a $ on a cell definition, but i can't quite seem to find it.

hey rob,

thanks for reviewing it... without seeing the actual worksheet, it would be difficult for you to see... i13 wasn't supposed to follow that same grab the next column pattern, it references a full amount for the invoice.

so...

this is still unsolved for me... i can't seem to figure out the issue.

well doing it blind i cant see where its supposed to go in the worksheet

=IF($B13="X","",IF(AND($R13>0,$R13<=Y$8,$R13>X$8),$S13,IF(AND($P13>0,$P13<=Y$8,$P13>X$8),$Q13,IF(AND($N13>0,$N13<=Y$8,$N13>X$8),$O13,IF(AND($L13>0,$L13<=Y$8,$L13>X$8),$M13,IF(AND($I13>0,$I13<=Y$8,$I13>X$8),$E13,IF(AND($H13<=Y$8,$H13>X$8),$E13,"")))))))

it may be total rubbish as i dont know what its supposed to do!

martin, can you view the workbook?

This formula is for a cashflow model to view incoming and outgoing cash on a weekly basis.

there are 3 different ways that a payment date can be calculated.

1. Based on the invoice due date
2. Based on the day I actually EXPECT them to pay
3. Based on a payment plan (with a maximum of 4 payments)

since each of those is an escalation over the last, the formula checks if there is a date in 1 of the 4 sections of the payment plan. then if there is a date, and it is within the current weeks date, it returns the value. if it isn't within the current week, it returns nothing. then it checks the expected pay date, if it's within the current week range, it returns the full invoice value, if there isn't a date in the expected category, it checks the due date and if it is within the current range, it returns the full invoice amount.

here's a copy saved as a 2003 format....

obviously it gave me a major error because of the length of the formulas.

but... i thought this way you might get the visual.

what cell is it in?

my problem is that it works on the first row, but when i copy it across and down it doesn't anymore.

from cells v13 across to at13, and then down 1000 rows.

if you did this manually what totals would you expect in
v,w,x,y 18?

DOH!
Sorry dude, I have 2007, I looked at this much earlier and couldn't figure out how it worked so I kinda walked away from it.
><
me sry.

Your formula works, you just need to enter an if statement for the cells with regard to reference cells being blank! It says "FALSE" because none of the arguments you have allowed for are being met. IE if it's this, do this, if it's that, do that, but if it's blank, none of the arguments are true, so it's showing "FALSE"

I guess Jason figured that out before me. ><

hmmm... ok, i think this does have something to do with conditions not being met, but i am not quite sure still (sorry, i have been away from this problem for a while)

Columns L, N, P, R - are the first sets of dates that it is supposed to look for. If these don't exist, it should then use the dates in Column I, and if there isn't one in that column, it should use Column H, then, if none of those conditions exist, it should display blank.

i've going through it again, hopefully i can figure it out.

nope, still the original... everytime i do something to it, and copy it across, if it doesn't work, then i go back to the start... i think things can get off track really fast revising and revising, etc.

Here's a thought... looking at this on paper in logical statements...
You start with
IF($R13>0,IF(AND($R13<=W$8,$R13>V$8),$S13
Now this says
if r13 is greater than 0
then continue on to see;
ifr13<=w8
and
if r13>v8
then s13
You then carry on to the next cell, P, then to N, then to L

Problem I can see in the logical argument on paper, is that you have 2 if statements for R13, yet you jump immedietly into the next group for P while only reconciling one of the "If not" criteria. What I mean to say is that I think it's going from

ifr13<=w8
and
if r13>v8
then s13
if not, then if P ... etc, etc...
But the original if R13>0 does not have a - If not - to close it.

I'm not sure if that's the problem you're having in excel, but I do know that looking at it on a page written the way I've typed it out, it's obvious that for each if statement, you need a if not statement, and only one is offered for each group.

So...
If R13<=w8
and
R R13>V8
then S13
if not........

There's the argument I think you're missing in each group.
At least it's the one that I can't figure out what is supposed to be.

I changed your formula slightly so that each group of if's referring to a single cell are individually nested in order to get rid of the FALSE problem you've had. Check out the attached worksheet and see if it's what you're looking for.

=IF($B15="X","",IF(AND($R15>0,$R15<=AQ$8,$R15>AP$8),$S15,IF(AND($P15>0,$P15<=AQ$8,$P15>AP$8),$Q15,IF(AND($N15>0,$N15<=AQ$8,$N15>AP$8),$O15,IF(AND($L15>0,$L15<=AQ$8,$L15>AP$8),$M15,IF($I15>0,(IF(AND($I15<=AQ$8,$I15>AP$8),$E15,(IF(AND($H15<=AQ$8,$H15>AP$8),$E15,""))))))))))

ok... sorry again... it was my birthday, so i was taking an excel vacation.

so, if a planned payment date is before the due date it shows both.... hmmm... i hadn't thought of this before... companies don't usually pay in advance... but, if it happens, the planned date should over ride the due date... since it was a manually entered number, and not a function of the invoice date plus the terms.

Got that resolved.

Last issue conceivable ( hate to say that, cause now there'll be others )

Is this possible -

No bill date,
no plan to pay date,
yet...
a payment it posted with a date.

I really think that if they post it, a planned to pay date should be posted along with it, but I'm not the one who has to use the thing, so lemme know!

I believe we've now covered - planned date before bill date, no planned date, no due date.

there will always be an bill date put into the system. The planned to pay date may or may not be entered... if a check is received before it was expected, it wouldn't have one.

The planned pay date is entered when a company has to call and say "hey, when are you planning on paying me?"