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 Eric Ewing Your Own Question

Eric Ewing
Eric Ewing, IT Pro
Category: Microsoft Office
Satisfied Customers: 194
Experience:  I am a technology professional with 18 years experience, and hold 15 industry certifications (from hardware/software repair to mobility to IT Security, and everything in-between!).
93256567
Type Your Microsoft Office Question Here...
Eric Ewing is online now
A new question is answered every 9 seconds

Need a formula in excel.That in cell R it should

Customer Question

Need a formula in excel.That in cell R it should calculate the following if it finds the words "Amazon - P" in cell H and the number amount higher than 5 in cell L it should add add a dollar to cell R.
Submitted: 7 months ago.
Category: Microsoft Office
Expert:  Eric Ewing replied 7 months ago.
Hi Customer! My name is ***** ***** I am an independent computer expert who's been paired with you through JustAnswer.com. It is my pleasure to assist you today.Might it be possible for you to attach the Excel file to this conversation so as that I can take a look? Barring that, here's a down-and-dirty formula that might work:*This will work ONLY if your cell R is empty, and you are wanting the value there to be either $1.00 or nothing. If your cell R has an existing value, you cannot conditionally take that value and then add $1.00 to it lest you create a circular reference (you would need to write a macro script to handle the calculation, in that case).*First, make sure your column R is set to the ACCOUNTING number format, so that it displays dollars and cents. Then paste this into R:=IF(AND((H#="Amazon - P"),(L#>5)),1,"")You will need to, of course, replace each"#" above with your corresponding row number. As you copy & paste the formula into other rows, it will update the row numbers accordingly.Will that work for you?
Customer: replied 7 months ago.
R is empty and the formula is not working at all
Customer: replied 7 months ago.
Oh i need to replace the #
Expert:  Eric Ewing replied 7 months ago.
Yep, that should help. ;-)
Expert:  Eric Ewing replied 7 months ago.
Any luck?
Customer: replied 7 months ago.
Its not working,let me rephrase, Only if it finds "Amazon - P" and in column L the number is ***** than 5 should it add one dollar to column R
Customer: replied 7 months ago.
And mind you, column R is empty rite now so I will be fine with your formula but i don't think it was built correctly.
Expert:  Eric Ewing replied 7 months ago.
That's correct. It works on my end exactly as described. Can you attach the spreadsheet for me to take a look at?
Expert:  Eric Ewing replied 7 months ago.
If you'd prefer, we can setup a remote help session whereby I can connect to your computer and work on the formula directly; however, that does cost a little extra and I could do it at zero extra cost if you're able to upload the file here.
Customer: replied 7 months ago.
Here
Expert:  Eric Ewing replied 7 months ago.
Received! Thanks. Just a moment or two.
Customer: replied 7 months ago.
I think i figured it out, hold on.
Customer: replied 7 months ago.
Its working.
Expert:  Eric Ewing replied 7 months ago.
Awesome! I tried this out in your spreadheet:=IF(AND((H274="Amazon - P"),(L274>5)),"$1.00","")Replacing with the appropriate row numbers. Is that what you also have?
Customer: replied 7 months ago.
Is there a way to add that if its anything else the number should be 0.00
Customer: replied 7 months ago.
Because if not, i cant use that cell to calculate further stuff, it comes up invalid value
Expert:  Eric Ewing replied 7 months ago.
Absolutely! Just add $0.00 into the last set of quotation marks. So, it should look similar to this:=IF(AND((H274="Amazon - P"),(L274>5)),"$1.00","$0.00")
Expert:  Eric Ewing replied 7 months ago.
Hi Customer: It's been a bit since we chatted...I just wanted to check back in and make sure the formula I provided worked for your purposes? Please let me know!