conditional formula

[Craigallen]

can someone please show me an example of a formula that would allow a value
to be placed in a cell from a formula I have written, only if the answer is
positive, otherwise it would put a zero as the answer? For example, if I had
a formula that added two cells together, if the result was a positive number,
I want that number in the cell, but if the answer is a negative number,
instead of that negative number, I want a zero in the cell.

 

[Mike H]

Try this:-

=MAX((A1+B1),0)

Mike

 

[Gord Dibben]

=IF(A1+B1<0,0,A1+B1) entered in any cell.


Gord Dibben MS Excel MVP

 

[Craigallen]

If I want the cell 2 to have the value of cell1 if that value is positive, or
a zero if that value is negative, what would that formula look like?

Also, let’s say I have a dollar amount that I need to pay in cell 1, and I
have two choices from which to pay it (cell 2 and cell 3). If I want a
formula that says take the money to pay cell one from cell 2 if there is
enough, but if there’s not enough, take the rest from cell 3, what would that
formula look like?

 

[Gord Dibben]

Please use proper cell addresses like A1 or A2 or G34 in place of cell1 cell2
etc. then re-post with what is in those cells and where you want some results.

Gord

 

[Craigallen]

Sorry Gord,

Let's say in A1 I have a formula that sums three cells that are expenses in
a budget to one dollar amount $550, and I want to use money from another cell
D5 which has $300 and E5 which has $800 to pay for it. If I want to have a
formula in A2 that tells the spreadsheet to take as much as possible from D5,
but if that's not enough, take the rest from E5, what would that formula look
like?

Thanks,

 

[Craigallen]

that first formula works perfectly, thanks!

 

[Gord Dibben]

You can't change the value of E5 using a formula in another cell if that's what
you are attempting.

Assuming A1 formula is =SUM(C1:C3) resulting in 550

D5 is 300

E5 is 800

In A2 enter =E5-(A1-D5) returns 550 which is what you have left to use.

But note: E5 value does not change.


Gord

 

[Craigallen]

let me ask it in a different way... what I'm trying to do is to say I have
money in two "accounts"; cells D5 and E5, and I want to pay a bill; the
amount of the bill is in A1. I want to pay the bill in A1 with money from D5
first, and if I run out of money in D5, I want to take the rest from E5. So,
I need a formula in A2 that will look at what is in A1 and then subtract as
much as is available from D5 until either the full amount in A1 is covered,
or D5 is reduced to $0, and then take the remaining balance from E5.

 

[Gord Dibben]

To actually subtract from and change D5 and E5 you would need event code as far
as I can see.

As I said..........formulas cannot change a fixed value in another cell.

D5 at 300 is a fixed value.

E5 at 800 is a fixed value.


Gord

 

[Craigallen]

I don't want the values in D5 or E5 to change; what I would like is a formula
in A2 that looks at D5 first and subtracts from D5 as much as needed to cover
the amount in A1, but if there's not enough, the formula in A2 would then
look at E5 for the balance. So let's say A1 has $300 in it; and let's say
there is $500 in D5; the formula would look at D5 and see that there is
enough in D5, so it would simply subtract $300 from D5, leaving a balance of
$200. But, if there was $550 in A1, then the formula would look at D5 and
see that there was only $500 there, so it would subtract the $500, and then
look to E5 for the other $50. I realize I may need formulas in more than
just A2 in this example to accomplish this. So, is there a combination of
formulas that would work?

 

[Roger Govier]

Hi Craig

In cell D2 enter
=MIN(D1,A1)*-1
in cell E2 enter
=MIN(E1,(A1+D2))*-1

I have made the numbers negative in each case, so you could carry out a
Sum on column D and E to get the available balance in each account.

 

[Gord Dibben]

You keep saying "subtract from D5" or "subtract from E5" which sounds like you
want those values reduced.

I don't understand that part.

Sounds like you want D5 or E5 to change without changing.

See Roger's answer for using a couple of helper cells.

Maybe that's what you need.


Gord