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, Computer Enthusiast
Category: Computer
Satisfied Customers: 19393
Experience:  Over 16 years of experience in the Electronics and IT industries.
Type Your Computer Question Here...
Pete is online now

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

This answer was rated:

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