Using XP and Excel 2003-how do I create a formula for the following outcome:
Cell A3 is nil, cell B3 has a value of say $500.00. I want to add cell B3 to
say cell B6 which has a value of say $1000, but if cell A3 has a value and B3
is nil I want to subtract A3 value from B6.

You don't say what to do if both A3 and B3 have a value.

Assuming that one of those cells has to be nil then use the formula B6+B3-A3

cheers,
Rusty

"maud0361" wrote in message
...
Using XP and Excel 2003-how do I create a formula for the following
outcome:
Cell A3 is nil, cell B3 has a value of say $500.00. I want to add cell B3
to
say cell B6 which has a value of say $1000, but if cell A3 has a value and
B3
is nil I want to subtract A3 value from B6.

Right-click on sheet tab and insert kode

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A3:B3")) Is Nothing Then Exit Sub
If [a3] = 0 And [b3] < 0 Then [b6] = [b6] + [b3]
If [a3] < 0 And [b3] = 0 Then [b6] = [b6] - [a3]
End Sub


"maud0361" skrev:

Using XP and Excel 2003-how do I create a formula for the following outcome:
Cell A3 is nil, cell B3 has a value of say $500.00. I want to add cell B3 to
say cell B6 which has a value of say $1000, but if cell A3 has a value and B3
is nil I want to subtract A3 value from B6.

Hi,
try this
=IF((AND(A3=0,B3<0)),B3+B6,IF((AND(A3<0,B3=0)),B 6-A3,"conditions not
meet")).

PS not sure how many nested IF Excel xp allows (i guess more than one, but i
never used it).
Andrea

"maud0361" wrote:

Using XP and Excel 2003-how do I create a formula for the following outcome:
Cell A3 is nil, cell B3 has a value of say $500.00. I want to add cell B3 to
say cell B6 which has a value of say $1000, but if cell A3 has a value and B3
is nil I want to subtract A3 value from B6.

FYI

In XL2003 and lower the limit to nested functions (including IF) is 7
For XL2007 the limit is 256

--
Regards

Roger Govier


"Andrea" wrote in message
...
Hi,
try this
=IF((AND(A3=0,B3<0)),B3+B6,IF((AND(A3<0,B3=0)),B 6-A3,"conditions not
meet")).

PS not sure how many nested IF Excel xp allows (i guess more than one,
but i
never used it).
Andrea

"maud0361" wrote:

Using XP and Excel 2003-how do I create a formula for the following
outcome:
Cell A3 is nil, cell B3 has a value of say $500.00. I want to add
cell B3 to
say cell B6 which has a value of say $1000, but if cell A3 has a
value and B3
is nil I want to subtract A3 value from B6.

"excelent" can I type the code into the cell ? if not were do I type It?
maude0361

"excelent" wrote:

Right-click on sheet tab and insert kode

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A3:B3")) Is Nothing Then Exit Sub
If [a3] = 0 And [b3] < 0 Then [b6] = [b6] + [b3]
If [a3] < 0 And [b3] = 0 Then [b6] = [b6] - [a3]
End Sub


"maud0361" skrev:

Using XP and Excel 2003-how do I create a formula for the following outcome:
Cell A3 is nil, cell B3 has a value of say $500.00. I want to add cell B3 to
say cell B6 which has a value of say $1000, but if cell A3 has a value and B3
is nil I want to subtract A3 value from B6.

This is an event macro.

If you want to try, rightclick on the worksheet tab that should have this
behavior. Select view code and paste the code into the newly opened code window
(on the right).

Then back to excel to test it out.

I'd try it in a test worksheet in a new workbook to see if it behaves the way
you want. (I didn't follow this thread.)

maud0361 wrote:

"excelent" can I type the code into the cell ? if not were do I type It?
maude0361

"excelent" wrote:

Right-click on sheet tab and insert kode

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A3:B3")) Is Nothing Then Exit Sub
If [a3] = 0 And [b3] < 0 Then [b6] = [b6] + [b3]
If [a3] < 0 And [b3] = 0 Then [b6] = [b6] - [a3]
End Sub


"maud0361" skrev:

Using XP and Excel 2003-how do I create a formula for the following outcome:
Cell A3 is nil, cell B3 has a value of say $500.00. I want to add cell B3 to
say cell B6 which has a value of say $1000, but if cell A3 has a value and B3
is nil I want to subtract A3 value from B6.

--

Dave Peterson