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: 18586
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: 1 year ago.
Category: Computer
Customer: replied 1 year 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 1 year ago.
This file will help explain question. and I am available on 00 353 1(###) ###-####
thanksStephen
Expert:  Pete replied 1 year ago.
Hi Stephen,I'll be happy to assist with your Excel question.
Expert:  Pete replied 1 year 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 1 year ago.
Let me know if you would like remote assistance to go through this formula or any other formulas...
Pete and 4 other Computer Specialists are ready to help you
Customer: replied 1 year 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 1 year 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 1 year 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 1 year ago.
hi having problem with download, note I am on a mac 0SX
Expert:  Pete replied 1 year ago.
Ok, perhaps I can edit this formula and send it back then.Approximately how many product codes do you have?
Customer: replied 1 year ago.
approx 100
Customer: replied 1 year ago.
i could do a skype call if that helps!
Expert:  Pete replied 1 year 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 1 year 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 1 year 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 1 year ago.
nice one thanks, ***** ***** on that second question?
Expert:  Pete replied 1 year 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 1 year ago.
super thanks
Expert:  Pete replied 1 year 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 1 year 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 1 year ago.
Thanks just trying this at them moment
Expert:  Pete replied 1 year ago.
Let me know if you need anything else at all.Pete.
Customer: replied 1 year ago.
Pete thanks very much for all it seems to be working greatst