The file ID # XXXXX XXXXX
I don't think that there is any easy way to do that. The vlookup doesn't return any argument that says whether it found an exact match or gave you the nearest one. It also doesn't return the cell number that it found where you could have the formula check if it is equal and give the average if it is not.
You might be able to do it with index match but I would have to play with it for a bit. I haven't downloaded the sheet yet and it would be a complicate formula.
Let me know if you want me to do it because I doubt I would have an answer for you before tomorrow. Otherwise I will opt out.
I've read of it as a possibility using SUMPRODUCT, but I wasn't able to get it to work, I don't think I was understanding the formula enough. Do you know of any other formulas that might work besides INDEX-MATCH?
INDEX MATCH would seem to be the right one if it returns the value of the lowest one when it doesn't match AND tells you that it did not find the exact match. That would seem to be your biggest problem as you don't really know if its matching or not. And then you would have to use the cell reference plus 1 in the row field and might have to use another column to get it correct. I don't know of anything that would just do what you want it to do. It might be possible but it would be a pretty complicated expression.
I am willing to look at it when have some spare time tonight or tomorrow but that's all I can promise. I am happy to opt out and let somebody else do it if you prefer.
Based on what you're telling me, I'm wondering if it would be easier just to alter the original table and make it more precise. Now my question would be if there's an easy way to do that that you can think of, since I have about 10 sheets with 900 rows of numbers. Is there a way I can highlight a column and it'll put an extra number between each of the already-existing numbers? Everything is in ascending order, but inconsistent.
not off the top of my head
you might be able to create a macro to skip a line and insert a row and go down a line and insert a row etc
it might be easier to export the table to something else and import it again
you might export it to a text file and just go down hitting enter down enter down to create new rows that way and then read it back into excel
or if you have access you can pull it up as an access table and just add the numbers you want to the end then sort it and read it back
Actually you can do that through excel as well. rather than make spaces in the table
just add the new data to the bottom and then use the sort command under data to resort it into the correct order.
I'd like to avoid adding data individually though. That's about 900 numbers I'd be adding to each of the 10 sheets I have, and 9,000 entries will take me a while. Each sheet has a sounding in feet and a volume in cubic feet side by side for 900 rows. For the soundings, which are relatively consistent, I doubled the amount of soundings from the original data I have by using the "fill" feature and having a start and stop point. So now my soundings go for 1800 rows. I'd like to do something similar with the volumes relative to each of the soundings I expanded but the problem is that the volumes have a curve. They start off with big volume changes in each number and go like that until each volume change is almost non-existent. The last of the volumes have the same number for 5 or 6 soundings. I'd like a way for excel to calculate that curve so I can use the fill and put in the start and stop for each volume side by side with the soundings, and excel will fill in the volumes based on their change between each individual number. Or, if there's a way to have excel get an average number of every two cells in the column, I can add those to the end of the column like you were saying and put them in order.
the command would be something like AVG(B1,B2)
then copy that down
but do it in a cell not directly underneath so if you are using the B1,B2 put it in cell A900
then when its done do a copy and paste special / data only
into the cell underneath the table
then you can just sort that now larger table