if + sign

[kelvin_9]

Hi All,

i was thinking if i can use "if+sign" to return positive and negative value at column N?
thanks for the advice

hotel stroller demo 1.1.xlsx
	H	I	J	K	L	M	N
53	opening quantity	rental quantity	net balance	return quantity	closing quantity		variance
54	10	1	9	1	10		0
55	10	2	8	1	9		-1
56	9	3	6	5	11		-1
DLH

Cell Formulas
Range	Formula
H55:H56	H55	=IF(P54="","",IF(P54="no",L54,IF(OR(O54="/"),10,IF(AND(P54="yes",O54="park to hotel"),(L54-N54),IF(AND(P54="yes",O54="return to park"),(L54+N54))))))
J54	J54	=H54-I54
J55:J56	J55	=IFERROR(H55-I55,"")
L54	L54	=H54-I54+K54
L55:L56	L55	=IFERROR(H55-I55+K55,"")
N54:N56	N54	=IF(L54=10,0,IF(L54>10,(10-(L54)),((L54)-10)))

 

[kevin9999]

I'm clearly missing something obvious, but doesn't =L54-10 give you what you're looking for?

 

[kelvin_9]

I'm clearly missing something obvious, but doesn't =L54-10 give you what you're looking for?

thanks for your reply kevin9999

cell N54, correct, 0
cell N55, correct, -1
but N56 is not correct, if L56 not 10, 10-L56, which should +1

 

[kevin9999]

but N56 is not correct, if L56 not 10, 10-L56, which should +1

My formula would be (based on your sample data) =L56-10 which is 11-10 which is 1. Is that not correct?

 

[kelvin_9]

My formula would be (based on your sample data) =L56-10 which is 11-10 which is 1. Is that not correct?

thank you very much for your reply kevin9999

let me check first, one second

 

[kevin9999]

Book1
	L	M	N
1	closing quantity		variance
2	10		0
3	9		-1
4	11		+1
Sheet1

Cell Formulas
Range	Formula
N2:N4	N2	=L2-10

 

[kelvin_9]

Book1
	L	M	N
1	closing quantity		variance
2	10		0
3	9		-1
4	11		+1
Sheet1

Cell Formulas
Range	Formula
N2:N4	N2	=L2-10

thank you very much for your reply kevin9999

looks fine for my formula, but seems strange
how should i amend it(one more if?) to trigger positive value or using if+sign can be sloved?

hotel stroller demo 1.1.xlsx
	L	M	N
53	closing quantity		variance
DLH

hotel stroller demo 1.1.xlsx
	L	M	N
55	9		-1
56	11		-1
DLH

Cell Formulas
Range	Formula
L55:L56	L55	=IFERROR(H55-I55+K55,"")
N55:N56	N55	=IF(L55=10,0,IF(L55>10,(10-(L55)),((L55)-10)))

 

[kelvin_9]

this?

=IF(L54=10,0,IF(L54>10,(10-(L54)),10-((L54))))

 

[kevin9999]

I am struggling to understand why you don't just use the (very) simple formula I showed you in post #6? If it doesn't give you the correct answer, then I just don't understand what your correct answer is...

 

[kelvin_9]

I am struggling to understand why you don't just use the (very) simple formula I showed you in post #6? If it doesn't give you the correct answer, then I just don't understand what your correct answer is...

thank you very much for your reply, kevin9999

your answer is great, but i have multiple query like below workbook
excel multiple if statments

thanks in advance