Microsoft Formula

  Abel 17:37 11 Nov 04

I’m having difficulty with an 'If' formula in Excel. What I want to achieve is, that if a condition in cell A1 is true, then the amount in cell B1 is reduced to zero. This part of the formula is OK. However, if the condition is false I need the amount in cell B1 to remain unchanged. What expression do I need in the formula to let the amount stand?


  dez fowler 19:29 11 Nov 04

What is it you're trying to achieve with this?

  Sir Radfordin 19:34 11 Nov 04

Think a bit more info will be needed on this one.

What data is being held in B1 normally?

What is the trigger for putting a value in A1.



Into B1 would mean that B1 would have a null value unless the condition on A1 were true but you couldn't also have a value in B1.

  Abel 21:43 11 Nov 04

Let’s assume there’s a value of 100 in cell B1. If the condition in cell A1 is ‘Paid’, I want to reduce cell B1 to zero. However, if there’s no data in cell A1, I want cell B1 to remain unchanged. A number of lines will be utilised in each column and each of these columns will be further analysed and totalled using the ‘SUMIF’ formula.

I do hope this explanation makes a little more sense than my previous thread?


  VoG II 22:07 11 Nov 04

As Sir Radfordin said, you can't have both a value and a formula in B1.

You will need to rethink the design of your worksheet.

That is unless B1 is getting its value from elsewhere, in which case we neen to know where/how.

  Sir Radfordin 08:50 12 Nov 04

Your explination makes sense however as VoG™ says we'll need to know where B1 gets it's value from originally. If it is just being typed in then you will not be able to do what you are asking. If however it is the sum of another data set then you will be able to create a nested forumla to do what you want.

  newearwax 09:24 12 Nov 04

Are you trying to extract the actual amounts outstanding from a list B1....B10, say?. The sumif function may be an answer
Range A1:A10 Criteria "" Sum Range B1:B10

  Abel 09:38 12 Nov 04

Thank you all for your help. Clearly, as suggested, I must re-think my spreadsheet.


This thread is now locked and can not be replied to.

Hands-on: Acer Predator Triton 700 review

D&AD Awards 2017: see the best design, advertising, illustration, animation and VR of the past year

How to lose weight with an Apple Watch