How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask flopcat98 Your Own Question

flopcat98
flopcat98, Consultant
Category: Microsoft Office
Satisfied Customers: 7152
Experience:  degree in comp sci. 20+ years install work with networks for small and large companies
Type Your Microsoft Office Question Here...
flopcat98 is online now
A new question is answered every 9 seconds

Im using VLOOKUP to reference a bunch of tables I have on

Resolved Question:

I'm using VLOOKUP to reference a bunch of tables I have on several sheets. It's looking up a sounding number, and sending back the corresponding volume to that sounding. It works, but I want to improve precision in the answer. I'd like to know if there's a way to use VLOOKUP where if it doesn't find an exact result (comes up in between two different soundings), instead of giving me the "less than" result it averages the two closest results together (the less than and the greater than).
Submitted: 10 months ago.
Category: Microsoft Office
Expert:  Richard replied 10 months ago.
Thank you for your question, my name is Richard.

Can I look at this spreadsheet please? It will be easier.

- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"

Thanks

Customer: replied 10 months ago.

The file ID # XXXXX XXXXX


 

Customer: replied 10 months ago.
Relist: Incomplete answer.
I still haven't received an answer.
Expert:  flopcat98 replied 10 months ago.

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.

Customer: replied 10 months ago.

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?

Expert:  flopcat98 replied 10 months ago.

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.

Customer: replied 10 months ago.

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.

Expert:  flopcat98 replied 10 months ago.

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

Expert:  flopcat98 replied 10 months ago.

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.

Customer: replied 10 months ago.

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.

Expert:  flopcat98 replied 10 months ago.
Yes you should be able to do an average command for. Any 2 cells and than just copy that formula down
Expert:  flopcat98 replied 10 months ago.

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

flopcat98, Consultant
Category: Microsoft Office
Satisfied Customers: 7152
Experience: degree in comp sci. 20+ years install work with networks for small and large companies
flopcat98 and 2 other Microsoft Office Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional