Excel 2003 Formula

  Abel 19:07 03 Mar 2007
Locked

I'd be most grateful if anyone could tell why this simple formula (=IF(F7+D8-B8="","",F7+D8-B8) returns 'Value'. The exclamation character alongside the cell says 'A value used in the formula is of the wrong data type' Incidentally the formula seems OK in Lotus 123.

Abel

  VoG II 19:15 03 Mar 2007

What is in F7, D8 and B8?

Check that they are numbers and not numbers stored as text.

  TonyV 19:17 03 Mar 2007

Just a thought, have you too many ""? Or should there be a value after the second = sign? =IF((F7+D8-B8)=0,"",(F7+D8-B8)) for instance!

TonyV

  Abel 19:30 03 Mar 2007

Hello VoG™ I was hoping you would respond. F7 (previous balance)plus D8 (RECEIPTS) minus B8 (payments) As simple as that.

TonyV There's not a problem with the parentheses. Also, I've tried substituting one of the '""' for a '0' but still no joy.

Abel

  VoG II 19:36 03 Mar 2007

see if this works

=VALUE(F7)+VALUE(D8)-VALUE(B8)

  TonyV 19:39 03 Mar 2007

I've just tried my version of the formula and it works fine. Giving a blank cell if the answer is 0 and the correct figure if there is a positive answer there. I have tried your version again and get a zero showing if that is the answer and a figure if that is the answer. So, I am not too sure where you go from there! Unless of course there is something in the formatting of the cells.

Good luck.

TonyV

  daba 20:45 03 Mar 2007

I think the answer is that F7+D8-B8 could never equal "" (null)

It would evaluate to something.

I tried =IF(F7+D8-B8="","True","False") in a completely blank sheet and got the answer "False", even though F7, D8, and B8 are all unitialised cells.

You could try just =F7+D8-B8 and accept the 0 in the result cell.

If you don't want to see zeroes, you can turn them off in "Tools->Options" but that turns sll zero values off.

And if that isn't whatyou want, then you could try a conditional format in the result cells, that if the value is zero, set the text colour the same as the background.

  VoG II 21:04 03 Mar 2007

The formula

=IF(F7+D8-B8="","",F7+D8-B8)

works for me although I think it would be better as

=IF(F7+D8-B8=0,"",F7+D8-B8)

so I suspect that the error is related to some non-numeric data being in the referenced cells.

  Abel 11:26 07 Mar 2007

Hello all, I'm back again. Sorry about the delay but I'm recovering from recent hand surgery, on my right hand, which unfortunately has gone wrong. Nuff said!
VoG™ I think your suspicions are right! The data in D8 and B8 is link pasted from another worksheet where the cells are column totals, achieved by the SUM function. In other words, the totals are achieved by a formula which is then being link pasted. Is there a way of link pasting the values of D8 and B8 instead of what appears their formula?

Abel

  VoG II 11:29 07 Mar 2007

I'm not sure what you mean by 'link pasted'.

  Abel 11:56 07 Mar 2007

VoG™,It's a way of linking a fluctuating cell total in one worksheet with a cell in another. The procedure, is to copy let's say, cell A31 on worksheet 'A', then pasting the data into, say, cell A10 on worksheet 'B', using the 'Paste Special' facility and then the 'Paste Link' button in the bottom left-hand corner.

This left-handed typing's a bit slow!

Abel

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

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

InVision Studio takes on Adobe XD and Sketch

Camera tips to take better iPhone photos

Comment transformer un iPhone en borne Wi-Fi ?