Formula Not Showing Negative Values Correctly

[Frankinstyyn]

Hey all,

I have written 3 formulas that all work perfectly and show the correct value on the formula builder - screenshot below.

As you can see on the bottom: Result: -1834.94 & -820.4 - that's the correct numbers, but on my spreadsheet the both show £0.00.

I've tried changing the number format, but no joy..

Any help would be really appreciated, thanks.

 

[Joe4]

Are you doing this in Excel?

That formula builder does not look like the Excel formula builder I am used to seeing.

 

[Frankinstyyn]

Are you doing this in Excel?

That formula builder does not look like the Excel formula builder I am used to seeing.

Yes, 100% excel...

Details below

 

[Fluff]

Check that you don't have any circular reference errors.

 

[Joe4]

Ah, it is Excel for Mac. That is why it looks different, I suppose.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

What is the exact format you have applied to this cell? It looks like it may be one that shows all negatives as zeroes.
If the calculation returns a positive number, does it show it correctly.
Try changing the format of the cell to "General" and see what it returns.

If you still have issues, please post an image of your data and results, so we can see what it all looks like.

BTW, the SUM formula here is totally unnecessary.

SUM(N38+I38-C38)

SUM is used to add up a range of cells. If you are using + and - already in your formula, SUM is redundant and unnecessary.
You only need one or the other. In this case, since you have a minus in your formula, you would just get rid of the SUM part.

 

[Frankinstyyn]

Check that you don't have any circular reference errors.

Thanks, as far as I'm aware I don't.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula Not Displaying Negative Values Correctly
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Frankinstyyn]

Ah, it is Excel for Mac. That is why it looks different, I suppose.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

What is the exact format you have applied to this cell? It looks like it may be one that shows all negatives as zeroes.
If the calculation returns a positive number, does it show it correctly.
Try changing the format of the cell to "General" and see what it returns.

If you still have issues, please post an image of your data and results, so we can see what it all looks like.

BTW, the SUM formula here is totally unnecessary.

SUM(N38+I38-C38)

SUM is used to add up a range of cells. If you are using + and - already in your formula, SUM is redundant and unnecessary.
You only need one or the other. In this case, since you have a minus in your formula, you would just get rid of the SUM part.

Okay thanks, I'll get that updated - been a while since I've posted :D

Here's what I'm trying to do.

If the value of N4 is above 0, show 0 and take the remainder off O4 and show me if negative
If the value of O4 is above 0, show 0 and take the remainder off P4 and show me if negative

I'll post the whole sheet so you get an idea....

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2	Spent						Billed						Difference
3	One	Two	Three	Four	Total		One	Two	Three	Four	Total		One	Two	Three	Four	Total
4	$200.00	$200.00	$200.00	$200.00	$800.00		$300.00	$100.00	$100.00	$200.00	$700.00		$0.00	$0.00	$0.00	$0.00	-$203.77
Sheet1

Cell Formulas
Range	Formula
F4,R4,L4	F4	=SUM(B4:E4)
N4	N4	=IF(N4<0,0,SUM(H4-B4))
O4	O4	=IF(AND(N4>0,O4>0),0,SUM(N4+I4-C4))
P4	P4	=IF(O4<0,100,(J4-D4))
Q4	Q4	=(K4-E4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N4:R4	Cell Value	>0	text	NO
N4:R4	Cell Value	<0	text	NO

 

[Fluff]

You do have circular references. The formula in N4 referes to itself, as does the one in O4.

 

[Frankinstyyn]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula Not Displaying Negative Values Correctly
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Ahhh got it.

I've got the link here, can I edit my original question?

CROSSPOST LINK - EXCEL FORUM: Formula Not Displaying Negative Values Correctly