Nested IF - Yes another question. 1

[vlus]

Hello:

I read with interest several of the threads relating to the Nested IF limit of 7. I also tried to implement at least one of the suggestions that I understood, but it didn't work as hoped.

I have a column of 30 cells that tabulate a running balance. That number could be greater than or less than zero, and at any time there could be as few as 1 cell populated up to the total of 30.

I need to carry the last known populated cell to another cell on the sheet.

My original plan was to use a Nested IF, where IF the last cell was <>0, then use that cells number, otherwise check the cell above it, etc. Of course this crashed because there were more than 7 nested IFs.

I then tried &ing them together as suggested in one of the posts, something like this:

=IF(O30<>0,O30,&quot;&quot&IF(O29<>0,O29,&quot;&quot&IF(O28<>0,O28,&quot;&quot&IF(O27<>0,O27,&quot;&quotetc., thru Cell O1

This did not work either, since it somehow calculated a weird number, possibly because it tried to deal with each cell that had a balance, instead of just the last one.

I'm a pretty basic level Excel user. I don't understand VLOOKUP and don't even know if that would work, but I saw it mentioned in a few places here. So at this point, can someone just help me with a formula that would accomplish this task. I'm sure if I could see the formula in action, I could better understand it.

Thank you in advance for any light shed!

Vlus

 

[Mikeb1970]

Hi vlus,

I kept this simple so you could find out how it works
the orientation used is downwards, so the cell with highest rownumber will have priority.

1 0 -12
2 0
3 0
4 0
5 0
0
0
8 0
9 0
10 0
11 0
0
13 0
14 0
15 0
16 0
17 0
0
0
0
0
0
0
0
0
0
-12 -12
0
0
0

In column A you need your numbers

In cell B1 you have this formula wich you need to copy and paste down to cells B2:B30

=IF(SUM(A1:$A$30)=A1;A1;0)

in cell C1 you should have this formula

=IF(MAX(B1:B30)=0;MIN(B1:B30);MAX(B1:B30))

the last formula is so complicated because you also need to be able to find negative numbers

Hope this will work, out at least show you a path

Mike

 

[vlus]

Hello Mike:

Thank you for you answer. I will take a look at it and try to make it work. In the mean time, I tried another solution that I found that looked like a winner.

Someone in another post, referenced this tuturial:

http://www.cpearson.com/excel/nested.htm

which seemed like it would work using 5 sets of nested IFs.

However, the very first nested IF doesn't return anything at all, not even an error.

My test data looks like this (and I apologize there are not 30 rows as I indicated earlier, the data column ends on line 30):

49.00
64.00
368.00
8,817.21
8,755.85
8,735.85
10,035.79
-964.21
-984.21
-1,004.21
-1,024.21
-1,044.21
-1,064.21
-1,084.21
-1,104.21
-1,124.21
-1,144.21
-1,164.21
-1,184.21
-1,216.21
-1,237.21
-1,274.21
empty cell
empty cell


The number I need to carry across the sheet is the current last known running balance, of 1,274.21

I started my first nested IF set working from the bottom up as:

=IF(O30<>0,O30,IF(O29<>0,O29,IF(O28<>0,O28,IF(O27<>0,O27,IF(O26<>0,O26,IF(O25<>0,O25,IF(O24<>0,O24,&quot;any text&quot)))))

This &quot;should&quot; have produced -1,274.21 but it didn't produce anything at all.

Am I missing something with this nested table. I tried just using empty cell IF(O28<>0,O28,&quot;&quot and it worked fine.

The plan was to name the 5 sets of formulas per the tutorial, then use a nested IF statement with the 5 Named Formulas as the various values.

I hope I have explained this clearly, thanks.
Vic

 

[vlus]

Mike:

OK, I tried using your original reply, but I got an error, as follows:

My list of running balances starts at O6, so I changed your B1 formula (which I placed in S6) as follows:

=IF(SUM(A1:$A$30)=A1;A1;0)

to:

=IF(SUM(O6:$O$30)=O6;O6;0)

Now since I still dont quite understand the formula or what the $ signs mean, it just seemed logical to change the cells referenced to the correct ones. But that gives me an error. Excel highlites the middle O6

Vic

 

[Mikeb1970]

Change the $0$30 to $0$35

then it should work

but i think you should need to look at a more complicated formula whenever you get this working

you can mail me questions our your spreadsheet at excelxp@skynet.be

 

[vlus]

I might have fixed it... switched the ;'s to ,'s let me finish pasting and will know for sure

 

[vlus]

woo hoo! OK.... Mike.... first... many thanks!!! I got your original solution in place and working... I still don't quite understand it though. Would you be so kind as to break it down a little!

Thanks again!!!!
Vlus

 

[vlus]

Is there anywhere to give you votes or points, as in some forum places for good answers?

 

[Mikeb1970]

OK,
will do that tomorrow, 2am here

 

[KenWright]

Just in case it helps, the following will return the last numeric entry from a column:-

=LOOKUP(9.99999999999999E+307,A:A)

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

vlus - if you want to thanks someone, just click on the &quot;Thank xxxx fo0r this valuable post&quot; link at the bottom of teh appropriate thread

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[xlbo]

Also, if you are willing to do some background work, this formula can be made quite easy

This assumes your data starts in A1 and the cells at the bottom are EMPTY (as opposed to a zero length string)

You can go Insert>Name>Define
and enter:
=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),1)
instead of a range reference. Call this range sumRange and click on the Add button

Then, you formula is:

=INDIRECT(&quot;A&quot;&COUNT(sumRange))

What this does is create a DYNAMIC range name that expands and contracts as data is added / deleted

The COUNT(sumRange)
counts the number of numeric entries in the dynamic range - thus giving the row number of the last entry
The INDIRECT statement converts a string (text) reference to a range reference so if there were 20 entries, the INDIRECT formula would basically be doing the equivalent of

=INDIRECT(&quot;A&quot; & 20)
or - in laymans terms, give me the value that exists in A20


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[vlus]

Thank you all for your suggestions. While KenWright and xlbo 's suggestions appear &quot;cleaner&quot; I could get neither of them to work for my application. Probably a shortcoming on my end, I'm sure. However, I was able to get Mikeb1970's solution to work, and while it adds extra information to the spreadsheet (offscreen) it did the job.

Thanks again everyone!
vlus

 

[Mikeb1970]

Hi Vlus,

I will come back to you and explain it, just had a bit of an emergency in the family here

Mike