If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Offset formula
Hi
the reason for this is that COUNTA counts the formula result "" also as a non blank cell. Try the following instead to get the last numeric value in your range: =VLOOKUP(9.9999999E307,$X5:$X100) -- Regards Frank Kabel Frankfurt, Germany I have a spreadsheet that is updated on a weekly basis and I have a summary of the information at the bottom of the page. I only want to show the last figure in column X. I've been using the following formula.... OFFSET($X$5,COUNTA($X5:$X56)-1,0) This has worked until I put a formula in column X (the user used to have to fill this in as they went). Now my OFFSET formula isn't working. Is it because I now have an underlying formula there? I have this formula in column X so the user only has to enter the data in column W and column X calculates for them. IF(ISBLANK(W14),"",SUM(X13+W14)) Can anyone help?? --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Offset formula
Hi
sorry, my fault. Use =VLOOKUP(9.9999999E307,$X5:$X100,1) -- Regards Frank Kabel Frankfurt, Germany Hi Frank, I tried the VLOOKUP formula below but it's telling me that I've entered too few arguments. Thanks, Nan --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Offset formula
Frank meant to type LOOKUP(... not VLOOKUP(.... His coffee supply is running low as it is early evening in Germany now. Or has he already switched to bier? -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "Nan " wrote in message ... Hi Frank, I tried the VLOOKUP formula below but it's telling me that I've entered too few arguments. Thanks, Nan --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
Offset formula
|
#5
|
|||
|
|||
Offset formula
Bernard Liengme wrote:
Frank meant to type LOOKUP(... not VLOOKUP(.... His coffee supply is running low as it is early evening in Germany now. Or has he already switched to bier? Bernard lol no it's just Coke :-) Frank |
#6
|
|||
|
|||
Offset formula
=LOOKUP(9.99999999999999E+307,$X$5:$X$100)/COUNT($X$5:$X$100)
"Frank Kabel" wrote in message ... Hi untested but try =VLOOKUP(9.9999999E307,$X5:$X100,1)/SUMPRODUCT(--($X$5:$X$100"")) -- Regards Frank Kabel Frankfurt, Germany Thanks, Frank, it works!! I now have another, related question. Column X is calculating a cumulative total. I want to be able to calculate the average weekly total. I was using this formula =OFFSET($x,COUNTA($x5:$x56)-1,0)/COUNTA($x5:$x56) So I'm trying to take the result of your VLOOKUP formula and divide it by the total number of weeks for which we have data. Can I do something like that with the VLOOKUP? Thanks, Nan --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|