Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 20th 09, 02:49 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 64
Default NESTED IF FORMULA - IGNORE BLANK CELLS

I have a spreadsheet that is updated throughout the month and a column that
presents a summary of data entered-to-date. Is there a nested IF formula
that will look at the last non-blank cell and display the value. For instance:

IF E1="" THEN, D1, BUT IF D1 IS "", THEN C1, etc.... I would need a formula
through E1.

I was able to start this on my own but it got too complicated.

Thanks!!!!

  #2   Report Post  
Old May 20th 09, 03:10 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,124
Default NESTED IF FORMULA - IGNORE BLANK CELLS

for row 14 where you are looking for the last number in the row

=INDEX(14:14,0,MATCH(9999,14:14))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Evan" wrote in message
...
I have a spreadsheet that is updated throughout the month and a column that
presents a summary of data entered-to-date. Is there a nested IF formula
that will look at the last non-blank cell and display the value. For
instance:

IF E1="" THEN, D1, BUT IF D1 IS "", THEN C1, etc.... I would need a
formula
through E1.

I was able to start this on my own but it got too complicated.

Thanks!!!!


  #3   Report Post  
Old May 20th 09, 03:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 1,696
Default NESTED IF FORMULA - IGNORE BLANK CELLS

=LOOKUP(2,1/(1:1<""),1:1)

would return the last nonblank cell in row 1.

"Evan" wrote:

I have a spreadsheet that is updated throughout the month and a column that
presents a summary of data entered-to-date. Is there a nested IF formula
that will look at the last non-blank cell and display the value. For instance:

IF E1="" THEN, D1, BUT IF D1 IS "", THEN C1, etc.... I would need a formula
through E1.

I was able to start this on my own but it got too complicated.

Thanks!!!!

  #4   Report Post  
Old May 20th 09, 04:12 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 64
Default NESTED IF FORMULA - IGNORE BLANK CELLS

Think we're on the right track. So to be more specific.

A1=$1000
B1=$3000
C1=
D1=
E1=
F1=Display of last non-blank in row

Eventually, C1 through E1 will be filled in.

The formula you provided returned a #NUM when I entered it

"Sean Timmons" wrote:

=LOOKUP(2,1/(1:1<""),1:1)

would return the last nonblank cell in row 1.

"Evan" wrote:

I have a spreadsheet that is updated throughout the month and a column that
presents a summary of data entered-to-date. Is there a nested IF formula
that will look at the last non-blank cell and display the value. For instance:

IF E1="" THEN, D1, BUT IF D1 IS "", THEN C1, etc.... I would need a formula
through E1.

I was able to start this on my own but it got too complicated.

Thanks!!!!

  #5   Report Post  
Old May 20th 09, 04:30 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 64
Default NESTED IF FORMULA - IGNORE BLANK CELLS

Oh gosh. Hate to be pain, but I will also need the formula to ignore zeros
and be specific to A1:E1 (not just 1:1)

Evan

"Evan" wrote:

Think we're on the right track. So to be more specific.

A1=$1000
B1=$3000
C1=
D1=
E1=
F1=Display of last non-blank in row

Eventually, C1 through E1 will be filled in.

The formula you provided returned a #NUM when I entered it

"Sean Timmons" wrote:

=LOOKUP(2,1/(1:1<""),1:1)

would return the last nonblank cell in row 1.

"Evan" wrote:

I have a spreadsheet that is updated throughout the month and a column that
presents a summary of data entered-to-date. Is there a nested IF formula
that will look at the last non-blank cell and display the value. For instance:

IF E1="" THEN, D1, BUT IF D1 IS "", THEN C1, etc.... I would need a formula
through E1.

I was able to start this on my own but it got too complicated.

Thanks!!!!



  #6   Report Post  
Old May 20th 09, 04:32 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 64
Default NESTED IF FORMULA - IGNORE BLANK CELLS

I figured it out. Thanks so much!!!

"Evan" wrote:

Oh gosh. Hate to be pain, but I will also need the formula to ignore zeros
and be specific to A1:E1 (not just 1:1)

Evan

"Evan" wrote:

Think we're on the right track. So to be more specific.

A1=$1000
B1=$3000
C1=
D1=
E1=
F1=Display of last non-blank in row

Eventually, C1 through E1 will be filled in.

The formula you provided returned a #NUM when I entered it

"Sean Timmons" wrote:

=LOOKUP(2,1/(1:1<""),1:1)

would return the last nonblank cell in row 1.

"Evan" wrote:

I have a spreadsheet that is updated throughout the month and a column that
presents a summary of data entered-to-date. Is there a nested IF formula
that will look at the last non-blank cell and display the value. For instance:

IF E1="" THEN, D1, BUT IF D1 IS "", THEN C1, etc.... I would need a formula
through E1.

I was able to start this on my own but it got too complicated.

Thanks!!!!



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Drag formula - ignore blank cells Paul Excel Discussion (Misc queries) 1 April 2nd 09 05:25 PM
Nested IF that will ignore blank (empty) cells Struggling in Sheffield[_2_] New Users to Excel 10 February 9th 09 07:40 PM
Nested IF function to ignore cells with no data Struggling in Sheffield[_2_] New Users to Excel 1 February 9th 09 06:25 PM
how to make a formula ignore blank cells Snap Excel Worksheet Functions 6 June 8th 06 12:54 AM
Formula to calculate number of days & ignore blank cells Mifty Excel Discussion (Misc queries) 7 February 13th 06 10:36 PM


All times are GMT +1. The time now is 01:41 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017