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.

 

[Frankinstyyn]

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

Ah understood.

Shouldn't the values show in the cell though as they formulas are correct...

Is there a way around this - iterative calculations don't work correctly either.

 

[Fluff]

For N4 you could use

=MAX(0,H4-B4)

and for O4

=IF(N4>0,0,MIN(0,I4-C4))

 

[Frankinstyyn]

For N4 you could use

=MAX(0,H4-B4)

and for O4

=IF(N4>0,0,MIN(0,I4-C4))

Thanks, I don't think it's quite working.

If I add your formula and then update the values, the amount on O4 isn't correct, it should carry the £100, over to P4.

Also the value in N4 should show 0

ExcelHelp.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	Spent						Billed						Difference
3	One	Two	Three	Four	Total		One	Two	Three	Four	Total		One	Two	Three	Four	Total
4	$0.00	$200.00	$0.00	$200.00	$400.00		$300.00	$700.00	$100.00	$200.00	$1,300.00		$300.00	0	$100.00	$0.00	$400.00
5
Sheet1

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

 

[Fluff]

In that case you need to explain what the formula is meant to do.

 

[Joe4]

In that case you need to explain what the formula is meant to do.

I agree. Without having a full understanding of the entire flow, and how all the different columns are supposed to interact with each other, it is very difficult to help you.

 

[Frankinstyyn]

In that case you need to explain what the formula is meant to do.

Okay cool.

I want N4 to calculate H4-B4. If that value is above 0, take everything above 0 and take that off O4
Then O4 would calculate N4(anything above 0)+I4-C4, and anything above 0 to be taken off P4

The overall goal is check I'm breaking even for One, then profits to go into Two, then everything else that's 'profit' should be deducted from Three

 

[Frankinstyyn]

I agree. Without having a full understanding of the entire flow, and how all the different columns are supposed to interact with each other, it is very difficult to help you.

Yep, understood. Tried to clear up on my last reply :D

 

[Fluff]

I want N4 to calculate H4-B4. If that value is above 0, take everything above 0 and take that off O4
Then O4 would calculate N4(anything above 0)+I4-C4, and anything above 0 to be taken off P4

I'm afraid that means nothing to me.
A formula in N4 cannot take anything off another cell.
With the data in post#13 what should N4 & O4 be & why?

 

[Joe4]

Just to clarify what Fluff was saying, formulas can only return values to the cells that they are found in.
They cannot affect any other cells.

If you have a bunch of interrelated cells (which is why maybe you had the circular reference earlier), if it cannot be done within the current structure, it may require the use of some Helper Cells or VBA.
Of course, we cannot really tell you if that is the case here until we have a full understanding (i.e. answers to Fluff's questions).

 

[Frankinstyyn]

Sorry, it's hard to explain. Thanks for the persistence!

N4 and O4 should show 0, but if the value was above 0, that amount should be deducted from the other cell..

Hopefully this will help..

Client 1: Let's say I spent £100 (B4) but billed client 1 £300 (H4) I made profit of £200, this value would go in N4 but show me 0 as I've broken even and £200 into the next cell (O4)
Client 2: Then I spent another £100 (C4) but billed client 2 £300 (C4) I made profit of £200, this value would go in O4 but show me 0 as I've broken even
Client 3: This is the final calculation - I spent another £1000 (D4) but billed client 3 nothing (J4), this value should show everything deducted from the above totalling -£600

I really hope that makes sense!

Thank you