Formula creation

[Guest]

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.

 

[Rusty]

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

 

[Guest]

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:

 

[Guest]

Hi,
try this
=IF((AND(A3=0,B3<>0)),B3+B6,IF((AND(A3<>0,B3=0)),B6-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

 

[Roger Govier]

FYI

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

 

[Guest]

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

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]

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.)

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

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.