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 Pete Your Own Question

Pete
Pete, Computer Enthusiast
Category: Computer
Satisfied Customers: 16339
Experience:  Over 16 years of experience in the Electronics and IT industries.
30970729
Type Your Computer Question Here...
Pete is online now

I have a basic excel formula question? can you help?

Resolved Question:

hi I have a basic excel formula question? can you help?
Submitted: 10 months ago.
Category: Computer
Customer: replied 10 months ago.
(Posted by JustAnswer at customer's request) Hello. I would like to request the following Expert Service(s) from you: Live Phone Call. Let me know if you need more information, or send me the service offer(s) so we can proceed.
Customer: replied 10 months ago.
This file will help explain question. and I am available on 00 353 1(###) ###-####
thanksStephen
Expert:  Pete replied 10 months ago.
Hi Stephen,I'll be happy to assist with your Excel question.
Expert:  Pete replied 10 months ago.
The resultant numbers in Column C appear to be 'almost' correct, however they are rounded up or down.To fix this, change the format of Column C from General to Number, and it will then display the correct result.
Expert:  Pete replied 10 months ago.
Let me know if you would like remote assistance to go through this formula or any other formulas...
Pete, Computer Enthusiast
Category: Computer
Satisfied Customers: 16339
Experience: Over 16 years of experience in the Electronics and IT industries.
Pete and other Computer Specialists are ready to help you
Customer: replied 10 months ago.
Hi basically the problem is I need to get the right data sales next to the correct product inputted into column c where the code from column a = column e that the correct figurre from column g goes into column c.Probably easier to explain on a call!!
Customer: replied 10 months ago.
alternatively if it does make sense in terms of what `i am asking if you could put the correct formulae into column c and send back excell sheet and i could look from their!
Expert:  Pete replied 10 months ago.
Ok, I see you have accepted the remote assistance offer - to share your screen with me, please go to:http://www.justanswer.com/remotedesktop/join.aspx?Session=32df38dd-fa3a-44f2-a343-7a76d7ad0734
Customer: replied 10 months ago.
hi having problem with download, note I am on a mac 0SX
Expert:  Pete replied 10 months ago.
Ok, perhaps I can edit this formula and send it back then.Approximately how many product codes do you have?
Customer: replied 10 months ago.
approx 100
Customer: replied 10 months ago.
i could do a skype call if that helps!
Expert:  Pete replied 10 months ago.
Ok, I see the problem - the formula is taking the first product code that it comes to and using that approximate value.To use exact values, you must have "FALSE" in your VLOOKUP formula.Try the following in Cell C3 and copy it down:=VLOOKUP(A3,E$3:G$90,3,FALSE)
Customer: replied 10 months ago.
great thanks for that just 2 questions this raises1/ C14 and C15 are coming up as N/A can I get them to come up as 0 if the code isn't available?2/ Also in terms of E14 is a code that is not on left hand side is there a way to highlight all codes like this when I run through a full list?Thanks,
Stephen
Expert:  Pete replied 10 months ago.
1. Yes, you can use IFERROR to insert a zero if the result is #N/ASo change the formula to:=IFERROR(VLOOKUP(A3,E$3:G$90,3,FALSE),0)
Customer: replied 10 months ago.
nice one thanks, ***** ***** on that second question?
Expert:  Pete replied 10 months ago.
I have two ideas on it;a) You could insert a new column which would tell you "YES" or "NO" if the code is on the left hand side or not;You could then use that value in the new column in a conditional format to highlight any codes in column E that are not on the left hand side.b) it may be possible to just create the conditional format without the new column - I am trying to figure that out now...
Customer: replied 10 months ago.
super thanks
Expert:  Pete replied 10 months ago.
Ok, to make this work you can either:i) Highlight all codes that match in yellow, leaving the codes that don't exist in white;ii) or reverse this by highlighting all the codes in yellow first, then using conditional formatting to turn all matching codes white, so that the non-matching codes are left highlighted
Expert:  Pete replied 10 months ago.
So what I would suggest is the second one, where you highlight all codes in column E first, using yellow highlighting;then go to cell E3 and click on Conditional Formatting - New Rule - Use a formula to determine which cells to format, then enter the following conditional format: =VLOOKUP(E3,A:A,1,FALSE) then click OK and OK again.The first cell, E3, should turn white as it is a match with a value in column A.To copy this conditional format to the rest of the cells in Column E, use the Format Painter and drag the formatting down the column.
Customer: replied 10 months ago.
Thanks just trying this at them moment
Expert:  Pete replied 10 months ago.
Let me know if you need anything else at all.Pete.
Customer: replied 10 months ago.
Pete thanks very much for all it seems to be working greatst

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
< Previous | 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
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
  • 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
  • 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
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin
  • 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
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
 
 
 

Meet The Experts:

 
 
 
  • Kamil Anwar

    Kamil Anwar

    Computer Support Specialist

    Satisfied Customers:

    151
    8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
< Previous | Next >
  • http://ww2.justanswer.com/uploads/JA/JACUSTOMERf8udkdxk/2013-8-3_15150_323738101505074393259301621172992o.64x64.jpg Kamil Anwar's Avatar

    Kamil Anwar

    Computer Support Specialist

    Satisfied Customers:

    151
    8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
  • http://ww2.justanswer.com/uploads/WI/Windowspcfix/2013-8-19_153826_ja12.64x64.jpg James K.'s Avatar

    James K.

    Consultant

    Satisfied Customers:

    93
    Technical Director of IT Company
  • http://ww2.justanswer.com/uploads/JO/JonTech/2011-10-25_111835_A2c.64x64.jpg Jon-Tech's Avatar

    Jon-Tech

    IS Manager

    Satisfied Customers:

    61
    20 yrs: Computers, Networking
  • http://ww2.justanswer.com/uploads/MI/mikew4950/2011-12-26_1606_mikephoto2s.64x64.jpg MikeW's Avatar

    MikeW

    Computer Engineer

    Satisfied Customers:

    53
    32+ years Computer and Networking Experience
  • http://ww2.justanswer.com/uploads/JA/jamieratliff/2012-2-8_194551_jamie.64x64.jpg jamieratliff's Avatar

    jamieratliff

    Information Systems Manager

    Satisfied Customers:

    21
    Jamie has worked in the Information Technology field for over a decade.
  • http://ww2.justanswer.com/uploads/JA/JACUSTOMERleg4q7o8/2012-3-6_203036_meja.64x64.png TheDoctor's Avatar

    TheDoctor

    Software Engineer

    Satisfied Customers:

    16
    Experienced Software Engineer
  • http://ww2.justanswer.com/uploads/FI/FisherEngineering/2012-1-18_194422_Fisher375Headshotcompressed239K.64x64.jpg Bill Fisher's Avatar

    Bill Fisher

    Computer Engineer

    Satisfied Customers:

    1
    Computer system design, networks, integration, general support.