Excel/Excel formula question
QUESTION: Hi, hope I can explain this without a diagram, but here goes. I'm looking for a forumal to assist my spreadsheet work.
In column A, line 1 I have a quoted figure of £100.
Then in column B line 1, I put the letter Y (for yes).
When I put the letter Y into that column I would like the quoted £100 to appear in column C.
If this is possible, I would also like it so that if I put a letter N (for no) into that cell (instead of Yes) then a standard (never changing) total of £70.00 is added to column C.
I currently do have the latter set up, and the formula is placed in the C column (the totals column) and is this: - =if(a="N",70,0)
This means that when I answer N, I get £70 in the total column. So., Ideally I would like to get the quoted amount (which would be in column A, line 1) to be added to the totals column if the answer is Yes, and/or
the 70.0 added to to the totals if the answer is N.
Hope this makes sense!
I am using Excel 2003.
ANSWER: I found this question in the question pool which meant that the expert you originally asked was unwilling or unable to answer the question- the question does make perfect sense, and I THINK you actually have three options - you have a Y in the cell, or an N in the cell (your two options) or it's blank - catering for all three gives you
you may want to replace the ,70,0 with ,70,"" which would give a blank cell rather than a zero value.
I hope this answer makes sense, but if you need any more help on this or anything else please do ask.
---------- FOLLOW-UP ----------
QUESTION: Hi, thanks a lot for your help. That equation works, and I'm happy with it, however, I'm going to try and see if poss to go even better.
Ok, using that formula above gives me the final solution in the totals column (C)of either the £70,00, or the quoted price. That's great. But, if on any occasion I should put a Y in column D, then I would like that final solution to appear not in column C, but instead in column J.
Am I being too silly here, or is this possible?
That's no problem
In column C
which would make D1 override B1
In column J
I wasn't sure if you needed the N bit in column J,but hopefully you can modify if needs be.