| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get the last displayed number in a row of columns
I have a spreadsheet that has data in row 4, colums B thru G. There are
formulas in these cells that get numbers from other cells (if applicable - sometimes these cells are blank). Sometimes the cells in the row might only contain numbers from B4 to D4. Other times the numbers might be in cells B4 to F4. What I need is to be able to put the last visible amount in row 4, wheter it be in column B,C,D,E,F or G into another cell... J4 for example. Any help would be appreciated. Jonco |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the last displayed number in a row of columns
One big nested if should do nicely...
J4 = If(G4<"",G4,If(F4<"",F4,If(E4<"",E4,If(D4<"",D 4,If(C4<"",C4,B4))))) Not tested could be missing a paren. -- Charles Chickering "A good example is twice the value of good advice." "jonco" wrote: I have a spreadsheet that has data in row 4, colums B thru G. There are formulas in these cells that get numbers from other cells (if applicable - sometimes these cells are blank). Sometimes the cells in the row might only contain numbers from B4 to D4. Other times the numbers might be in cells B4 to F4. What I need is to be able to put the last visible amount in row 4, wheter it be in column B,C,D,E,F or G into another cell... J4 for example. Any help would be appreciated. Jonco |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the last displayed number in a row of columns
I think this will also do what you ask.
=LOOKUP(MAX(B4:G4),B4:G4) "jonco" wrote: I have a spreadsheet that has data in row 4, colums B thru G. There are formulas in these cells that get numbers from other cells (if applicable - sometimes these cells are blank). Sometimes the cells in the row might only contain numbers from B4 to D4. Other times the numbers might be in cells B4 to F4. What I need is to be able to put the last visible amount in row 4, wheter it be in column B,C,D,E,F or G into another cell... J4 for example. Any help would be appreciated. Jonco |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the last displayed number in a row of columns
you can entering the following formula in cell J4
=OFFSET(B2,0,COUNT(B2:G2)-1) it counts the number of values in your range and offsets the starting cell to the number of values in the range giving you the last entered value. If you don't have any blank cells "JMB" wrote: I think this will also do what you ask. =LOOKUP(MAX(B4:G4),B4:G4) "jonco" wrote: I have a spreadsheet that has data in row 4, colums B thru G. There are formulas in these cells that get numbers from other cells (if applicable - sometimes these cells are blank). Sometimes the cells in the row might only contain numbers from B4 to D4. Other times the numbers might be in cells B4 to F4. What I need is to be able to put the last visible amount in row 4, wheter it be in column B,C,D,E,F or G into another cell... J4 for example. Any help would be appreciated. Jonco |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the last displayed number in a row of columns
according to the OP, the cells may sometimes be blank
I have a spreadsheet that has data in row 4, colums B thru G. There are formulas in these cells that get numbers from other cells (if applicable - sometimes these cells are blank). "dkinn" wrote: you can entering the following formula in cell J4 =OFFSET(B2,0,COUNT(B2:G2)-1) it counts the number of values in your range and offsets the starting cell to the number of values in the range giving you the last entered value. If you don't have any blank cells "JMB" wrote: I think this will also do what you ask. =LOOKUP(MAX(B4:G4),B4:G4) "jonco" wrote: I have a spreadsheet that has data in row 4, colums B thru G. There are formulas in these cells that get numbers from other cells (if applicable - sometimes these cells are blank). Sometimes the cells in the row might only contain numbers from B4 to D4. Other times the numbers might be in cells B4 to F4. What I need is to be able to put the last visible amount in row 4, wheter it be in column B,C,D,E,F or G into another cell... J4 for example. Any help would be appreciated. Jonco |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the last displayed number in a row of columns
Here is something that seems to work for me
=OFFSET(B2,0,COUNT(B2:G2)-1+COUNTBLANK(B2:G2)) david kinn "JMB" wrote: according to the OP, the cells may sometimes be blank I have a spreadsheet that has data in row 4, colums B thru G. There are formulas in these cells that get numbers from other cells (if applicable - sometimes these cells are blank). "dkinn" wrote: you can entering the following formula in cell J4 =OFFSET(B2,0,COUNT(B2:G2)-1) it counts the number of values in your range and offsets the starting cell to the number of values in the range giving you the last entered value. If you don't have any blank cells "JMB" wrote: I think this will also do what you ask. =LOOKUP(MAX(B4:G4),B4:G4) "jonco" wrote: I have a spreadsheet that has data in row 4, colums B thru G. There are formulas in these cells that get numbers from other cells (if applicable - sometimes these cells are blank). Sometimes the cells in the row might only contain numbers from B4 to D4. Other times the numbers might be in cells B4 to F4. What I need is to be able to put the last visible amount in row 4, wheter it be in column B,C,D,E,F or G into another cell... J4 for example. Any help would be appreciated. Jonco |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the last displayed number in a row of columns
If the data in B2:G2 is either numeric or blank (which is what I gather from
the OP), then Count(B2:G2)+Countblank(B2:G2) will always return 6. For me, the formula always returns whatever is in cell G2. In reviewing the thread, I see an error w/my formula. It should have been: =LOOKUP(MAX(B4:G4)+1,B4:G4) "dkinn" wrote: Here is something that seems to work for me =OFFSET(B2,0,COUNT(B2:G2)-1+COUNTBLANK(B2:G2)) david kinn "JMB" wrote: according to the OP, the cells may sometimes be blank I have a spreadsheet that has data in row 4, colums B thru G. There are formulas in these cells that get numbers from other cells (if applicable - sometimes these cells are blank). "dkinn" wrote: you can entering the following formula in cell J4 =OFFSET(B2,0,COUNT(B2:G2)-1) it counts the number of values in your range and offsets the starting cell to the number of values in the range giving you the last entered value. If you don't have any blank cells "JMB" wrote: I think this will also do what you ask. =LOOKUP(MAX(B4:G4),B4:G4) "jonco" wrote: I have a spreadsheet that has data in row 4, colums B thru G. There are formulas in these cells that get numbers from other cells (if applicable - sometimes these cells are blank). Sometimes the cells in the row might only contain numbers from B4 to D4. Other times the numbers might be in cells B4 to F4. What I need is to be able to put the last visible amount in row 4, wheter it be in column B,C,D,E,F or G into another cell... J4 for example. Any help would be appreciated. Jonco |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| empty columns not to be displayed | Charts and Charting in Excel | |||
| Find All Columns Displayed | Excel Worksheet Functions | |||
| Fomatting based on columns displayed | Excel Programming | |||
| Count number of times a specific number is displayed in a cell ran | Excel Worksheet Functions | |||
| Count number of times a specific number is displayed in cells | Excel Programming | |||
Linear Mode
