You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Excel formula question

Advertisement

QUESTION: Hi, hope I can explain this without a diagram, but here goes. I'm looking for a forumal to assist my spreadsheet work.

Lets say,

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

=IF(B1="Y",A1,IF(B1="N",70,0))

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

=IF(D1="Y","",if(B1="Y",A1,IF(B1="N",70,0)))

which would make D1 override B1

In column J

=IF(D1="Y",A1,IF(D1="N",70,0))

I wasn't sure if you needed the N bit in column J,but hopefully you can modify if needs be.

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.**Education/Credentials**

I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!