More formula help

[alan147]

Hello

I have a couple of cells that may occasionally contain numeric data A1 and B1, if B1 contains data that data should be put in to C1, if both contantain data then C1 should be populated with the value of A1 subtracted from B1.

Can this be done?

Thanks

Alan

 

[xlbo]

in C1:

=B1-A1

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ekrouse]

Put the following formula in cell c1 and copy down to additional rows. Please note, per your request this only works with a numeric value in b1 or a1&b1. If a value exists in a1 only, c1 will remain blank.

=IF(AND(CELL("type",B1)="v",CELL("type",A1)="v"),B1-A1,IF(CELL("type",B1)="v",B1,""))

 

[AnotherHiggins]

ekrouse,

No offense, but your formula seems kind of full of sound and fury, while signifying nothing (to paraphrase the Bard).

All I mean is that you seem to have gone through a lot of gyrations just to hide the zero that Geoff's simple formula returns on occasion.

We can get the same results as your formula by using the much less unwieldy (IMHO) [COLOR=blue white]=IF(B1=0,"",B1-A1)[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[ekrouse]

Depending on the application, your formula works just fine. However, if Alan's spreadsheet contains text data in column "b" (i.e. b1 = "cat", "dog", "username", etc) your formula would return an error.

 

[AnotherHiggins]

Good point. But then again, [COLOR=blue white]"dog" - 1[/color] isn't going to work very well!

I kid. I realize that your formula would recognize that the cell isn't numeric and therefore return a zero-length string, but it seems to me that if the OP needed to account for such a situation, he would have mentioned it instead of just saying, "A1 subtracted from B1".

In the end, I think the OP didn't provide enough information for anyone to completely understand his needs (Like will A1 ever be populated when B1 is not?).

So, Alan, do any of these formulae work for you?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

If there is a need for such a situation, I'd just use N(B1).

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

also...
[tt]
=SUBTOTAL(9,A1)-SUBTOTAL(9,B1)
[/tt]


Skip,
[sub]
[red][/red]
[/sub]

 

[firefytr]

Why would you use the Subtotal function if there is no AutoFilter turned on?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

just another way to work around STRINGS intermixed with numbers.

Skip,
[sub]
[red][/red]
[/sub]

 

[firefytr]

You know, I didn't realize that was inherent of the SUBTOTAL function. Learn something new everyday! Thanks Skip!

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[alan147]

Thanks for the help.

Johns formula, =IF(B1=0,"",B1-A1), works an is much less complicated than what I came up with which is =IF(OR(A1<>"",B1<>""),SUM(B1-A1),""). What I am trying to avoid is the error message #VALUE! if A1 is an empty cell. If there is data in A1 there will always be data in B1. A1 will only ever be less than or equal to B1. The data will always be numeric. The spread sheet is an attempt to work out overtime rates.

Thanks

Alan

 

[KenWright]

No reason you should get #VALUE that i can think of, so I'm with Geoff's original formula

=B1-A1

and if you don't want a 0 to show then with a custom format of something like

0.00;-0.00;""

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------