You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- What if scenario for subtraction

Advertisement

QUESTION: Hi Aidan,

I hope you can help me.

I am trying to write a what if scenario as per below.

I want to write:-

If cell Q7 is blank, do the following, N7 - O7 + Q7, but if Q7 is not blank, N7 - O7 + R7.

How do I put this into a formula?

Thanks

ANSWER: =if(isblank(q7),n7-o7+q7,n7-o7+r7)

or possibly better (because it's shorter)

=n7-o7+if(isblank(q7),q7,r7)

Having said that, I suspect you MAY have the test for q7 the wrong way round as in your explanatiion you are adding a blank value? If that is the case, just swap round the q7 and r7 parts of the formula.

---------- FOLLOW-UP ----------

QUESTION: Brilliant, thanks for this.

ANSWER: You are very welcome - anything else I can help with, always happy to do so!!

---------- FOLLOW-UP ----------

QUESTION: ok another situation,

I am using the following formula:-

=SUM(SUMIF(OFFSET(Sheet1!B2:B6,0,{0,2}),"Prod.A",OFFSET(Sheet1!C2:C6,0,{0,2})))

To explain what I am trying to do, using two separate worksheets is use a reference, called the product description, on the first sheet, and reference that on the second sheet, called wk1 and count the number of packs we plan to produce during that week and add the results.

The second sheet is laid out as follows:-

Line 1 Line 2 Line 3 Line 4 Line 5 Line 6

Prod Desc. Qty

Monday

Tuesday

Wednesday

Thursday

Friday

Each line has two columns, one for the product description and the second for the quantity. I want to search the columns under product description for the 6 Lines, and when it appears add the cell immediately to the right and put it back into the first sheet.

Problem with the first formula I have been given is that it is returning a 0 value?

Can you help?

Thanks for your assistance by the way.

the formula doesn't need the sum - just the sumif - but your formula doesn't seem to do what you described? is it possible to see a sample workbook? My email is aidan.heritage@virgin.net - having said that, it sounds as though a sumif on it's own would do it AS LONG as the second sheet only has this weeks data on it?

=sumif(wk1!RangeWithProductDescription,ProductDescriptionNameFromThisSheet,wk1!RangeWithQty)

replace my lengthy names with the actual ranges and cells and it should work based on how I read the problem - though re-reading it you need to have 6 rows across - for the SIMPLEST method, I'd simply have a very long formula - repeat my formula 5 more times, referring to the other ranges - this ISN'T the only solution, but it would work!

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!