Avoiding nested if statements?

[lonnierudd via OfficeKB.com]

I"ve asked this question before and didn't get an answer, so I thought I'd
phrase it differently. Maybe it isn't possible? If I can't get it to work in
VBA, then even if it has to be in regular formulas that I can run a macro on
that's okay. I would really appreciate some help.

I have a spreadsheet that has formulas that change based on the date that's
input into cell B1. Right now I'm up to this formula and I'm only 5 months
into the If's (that's Nov 08). I have to go to Dec 2010:

=IF(($D$16=$B$1),B13,IF(($D$17=$B$1),B12,IF(($D$18=$B$1),B11,IF(($D$19=$B$1),
B10,IF((B20=B1),(E18-$H$4),B13)))))

If it helps I've made a recreation of the spreadsheet below (not pretty, but
copying and pasting into the forum messed it up a little). The rows are
numbered on the left, with the Column letters on the top. I also put what the
formulas were before I started trying to nest the formulas.


A B C D E
1 31-Jul
2
3
4 Period Value Formula
in col E Bef Ifs
5 Aug-07 $18,848 (no formula
here)
6 Sep-07 $18,320 'E5-
$H$4
7 Oct-07 $17,792 'E6-
$H$4
8 Nov-07 $17,264 'E7-
$H$4
9 Term Value Dec-07 $16,736 'E8-$H$4
10 1 $13,905 Jan-08 $16,208 'E9-$H$4
11 2 $12,920 Feb-08 $15,680 'E10-$H$4
12 3 $12,015 Mar-08 $15,152 'E11-$H
$4
13 4 $11,455 Apr-08 $14,623 'E12-$H
$4
14 5 $11,095 May-08 $14,095 'E13-$H
$4
15 6 $11,060 Jun-08 $13,567 'E14-$H
$4
16 7 $11,200 Jul-08 $13,039 'E15-
$H$4
17 8 $11,010 Aug-08 $13,905 'B10
18 9 $10,580 Sep-08 $12,920 'B11
19 10 $10,160 Oct-08 $12,015 'B12
20 11 $9,705 Nov-08 $11,455 'B13
21 12 $9,210 Dec-08 $11,095 'B14
22 13 $8,665 Jan-09 $11,060 'B15
23 14 $7,985 Feb-09 $11,200 'B16

 

[Bernie Deitrick]

Lonnie,

You need to describe, in words, what it is that you want to do. Your table doesn't make immediate
sense (what is in H4?), so it is impossible to decipher just what it is that you want to do.

HTH,
Bernie
MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

I'm not 100% sure I got this right, but does this formula placed in E6 and
copied down do what you want?

=IF(C6="","",IF(C6<=B$1,E5-$H$4,INDEX(B$10:B$1000,1+DATEDIF(B$1,C6,"m"))))

Where I am assuming you update the date in B1 monthly. By the way, if that
is what you are doing (and if the formula does what you want), you can
replace the two occurrences of B$1 with TODAY() and the formula should work
without you having to update B1 on a monthly basis.

Rick

 

[Bob Phillips]

=INDEX($B$2:$B$13,13-MATCH($B$1,$D$16:$D$27,0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[lonnierudd via OfficeKB.com]

Sorry, but I could have been more clear. Cell H4 is the difference in the
market value by month on the vehicle. On what I posted H4 is $528.07 for the
month of July, 2008. On the table in cell B1 is entered the last day of the
month we're looking at. This time it's July 31, 2008, so the formula needs to
be pulled down from the cell beside June 2008, relatively speaking. In June
the formula is E14-$H$4, so in July it's E15-$H$4. The month after the date
we're looking at needs to equal what is in cell B10, in this case that would
be what is beside August 2008. Also, the cells below that need to change also,
so that September equals B11, October equals B12, etc. Next month, the
formula next to August will be E16-$H$4, and September will equal B10,
October will equal B11, etc. Does that make sense?

Lonnie,

You need to describe, in words, what it is that you want to do. Your table doesn't make immediate
sense (what is in H4?), so it is impossible to decipher just what it is that you want to do.

HTH,
Bernie
MS Excel MVP

I"ve asked this question before and didn't get an answer, so I thought I'd
phrase it differently. Maybe it isn't possible? If I can't get it to work in

[quoted text clipped - 47 lines]

22 13 $8,665 Jan-09 $11,060 'B15
23 14 $7,985 Feb-09 $11,200 'B16

 

[Rick Rothstein \(MVP - VB\)]

By the way, I have assumed that Column C contains *real* dates simply
formatted to look like you showed.

Rick

I'm not 100% sure I got this right, but does this formula placed in E6 and
copied down do what you want?

=IF(C6="","",IF(C6<=B$1,E5-$H$4,INDEX(B$10:B$1000,1+DATEDIF(B$1,C6,"m"))))

Where I am assuming you update the date in B1 monthly. By the way, if that
is what you are doing (and if the formula does what you want), you can
replace the two occurrences of B$1 with TODAY() and the formula should
work without you having to update B1 on a monthly basis.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Just out of curiosity, did you see the formula I posted 15 minutes before
your response to Bernie? I am pretty sure it does what you want.

Rick

Sorry, but I could have been more clear. Cell H4 is the difference in the
market value by month on the vehicle. On what I posted H4 is $528.07 for
the
month of July, 2008. On the table in cell B1 is entered the last day of
the
month we're looking at. This time it's July 31, 2008, so the formula needs
to
be pulled down from the cell beside June 2008, relatively speaking. In
June
the formula is E14-$H$4, so in July it's E15-$H$4. The month after the
date
we're looking at needs to equal what is in cell B10, in this case that
would
be what is beside August 2008. Also, the cells below that need to change
also,
so that September equals B11, October equals B12, etc. Next month, the
formula next to August will be E16-$H$4, and September will equal B10,
October will equal B11, etc. Does that make sense?

Lonnie,

You need to describe, in words, what it is that you want to do. Your
table doesn't make immediate
sense (what is in H4?), so it is impossible to decipher just what it is
that you want to do.

HTH,
Bernie
MS Excel MVP

I"ve asked this question before and didn't get an answer, so I thought
I'd
phrase it differently. Maybe it isn't possible? If I can't get it to
work in

[quoted text clipped - 47 lines]

22 13 $8,665 Jan-09 $11,060
'B15
23 14 $7,985 Feb-09 $11,200
'B16

 

[lonnierudd via OfficeKB.com]

Well, my pasting and explanation weren't very good in the original table, so
neither of these worked completely (my fault. I only pasted part of the table
and it didn't line up right). The table runs from B10 to B45 and changes each
month, and there isn't anything in Column C (Sorry again). I changed the
INDEX to reflect B10:B45 on Bob's answer, and took the $ signs off and
adjusted the length of the column a little and drug it down and it gives the
correct answer to July 2009 (Woo hoo!), although I must admit I am at a loss
as to understanding why and there's still a year and a half to go. Here's the
formula I used based on what Bob gave:

=INDEX($B$10:$B$45,13-MATCH($B$1,D5:$D$45,0))

Everything from August 2009 to December 2010 has #N/A on it now, probably
because the data ends on D45 I guess? I've never used the index and match
functions before. I need to look into those...

Rick's solution didn't work, but again, I think it was because the help I
tried to give with the columns and rows wasn't clear.

=INDEX($B$2:$B$13,13-MATCH($B$1,$D$16:$D$27,0))

I"ve asked this question before and didn't get an answer, so I thought I'd
phrase it differently. Maybe it isn't possible? If I can't get it to work

[quoted text clipped - 71 lines]

23 14 $7,985 Feb-09 $11,200
'B16

 

[lonnierudd via OfficeKB.com]

Well, it only looks like it works until July 2009. When I change the date the
other months are incorrect. What does the 13-Match do?

Well, my pasting and explanation weren't very good in the original table, so
neither of these worked completely (my fault. I only pasted part of the table
and it didn't line up right). The table runs from B10 to B45 and changes each
month, and there isn't anything in Column C (Sorry again). I changed the
INDEX to reflect B10:B45 on Bob's answer, and took the $ signs off and
adjusted the length of the column a little and drug it down and it gives the
correct answer to July 2009 (Woo hoo!), although I must admit I am at a loss
as to understanding why and there's still a year and a half to go. Here's the
formula I used based on what Bob gave:

=INDEX($B$10:$B$45,13-MATCH($B$1,D5:$D$45,0))

Everything from August 2009 to December 2010 has #N/A on it now, probably
because the data ends on D45 I guess? I've never used the index and match
functions before. I need to look into those...

Rick's solution didn't work, but again, I think it was because the help I
tried to give with the columns and rows wasn't clear.

=INDEX($B$2:$B$13,13-MATCH($B$1,$D$16:$D$27,0))

[quoted text clipped - 3 lines]

 

[Rick Rothstein \(MVP - VB\)]

Rick's solution didn't work, but again, I think it was because the

help I tried to give with the columns and rows wasn't clear.

Alright, assuming I got the column wrong (I still think the formula is
basically correct), put the following formula in D6 and copy it down (you
can copy it past your existing data if you want)...

=IF(C6="","",IF(C6<=B$1,D5-$H$4,INDEX(B$10:B$1000,1+DATEDIF(B$1,C6,"m"))))

Rick

 

[lonnierudd via OfficeKB.com]

Oh wow, it's close. I adjusted for the columns and here's what I got:

=IF(D6="","",IF(D6<=B$1,E5-$H$4,INDEX(B$10:B$45,1+DATEDIF(B$1,D6,"m"))))

This works up until the point where the formula is supposed to change to =B10,
then it hiccups. Here are two columns, the first of what it should be and the
second of what came out after the formula change. The table continues past
where it is needed, but I put it here so you can see what it's doing. The
first column is from B10 to B45, and the second is what it shows from E17 to
E45. From E5 to E16 is perfect.

$13,905 $12,920
$12,920 $12,920
$12,015 $11,455
$11,455 $11,455
$11,095 $11,060
$11,060 $11,200
$11,200 $11,200
$11,010 $10,580
$10,580 $10,580
$10,160 $9,705
$9,705 $9,705
$9,210 $8,665
$8,665 $7,985
$7,985 $7,985
$7,375 $6,990
$6,990 $6,990
$6,730 $6,690
$6,690 $6,745
$6,745 $6,745
$6,585 $6,280
$6,280 $6,280
$5,980 $5,660
$5,660 $5,660
$5,320 $4,945
$4,945 $4,485
$4,485 $4,485
$4,085 $3,825
$3,825 $3,825
$3,640 $3,600
$3,600
$3,605
$3,470
$3,260
$3,050
$2,830
$2,605

 

[Rick Rothstein \(MVP - VB\)]

I think we can resolve this rather quickly if I can only see what your
layout actually looks like. Can you send me a copy of your worksheet? Just
remove the NO.SPAM parts from my posted address
([email protected]) and the rest is my real address.

Rick

 

[lonnierudd via OfficeKB.com]

Thank you. It's on its way.

I think we can resolve this rather quickly if I can only see what your
layout actually looks like. Can you send me a copy of your worksheet? Just
remove the NO.SPAM parts from my posted address
([email protected]) and the rest is my real address.

Rick

Oh wow, it's close. I adjusted for the columns and here's what I got:

[quoted text clipped - 57 lines]

 

[Rick Rothstein \(MVP - VB\)]

For those following this thread, the OP sent me a copy of the workbook
offline. After looking at it, I suggested he try this formula...

=IF(D6<=B$1,E5-$H$4,INDEX(B$10:B$1000,DATEDIF(B$1-DAY(B$1)+1,D6-DAY(D6)+1,"m")))

in E6 and copy it down. He wrote back to me (offline) that the formula
worked, so I guess we can consider this question as having been answered.

Rick

Thank you. It's on its way.

I think we can resolve this rather quickly if I can only see what your
layout actually looks like. Can you send me a copy of your worksheet? Just
remove the NO.SPAM parts from my posted address
([email protected]) and the rest is my real address.

Rick

Oh wow, it's close. I adjusted for the columns and here's what I got:

[quoted text clipped - 57 lines]

Rick

 

[lonnierudd via OfficeKB.com]

And Rick is my hero...

For those following this thread, the OP sent me a copy of the workbook
offline. After looking at it, I suggested he try this formula...

=IF(D6<=B$1,E5-$H$4,INDEX(B$10:B$1000,DATEDIF(B$1-DAY(B$1)+1,D6-DAY(D6)+1,"m")))

in E6 and copy it down. He wrote back to me (offline) that the formula
worked, so I guess we can consider this question as having been answered.

Rick

Thank you. It's on its way.

[quoted text clipped - 10 lines]