excel If Function containing logical test from multiple dropdown list

[goldenparis]

Hey guys, I'm currently creating a room registry and encounter this error in one of my if function :error #value
I tried to googling this issue but failed without luck.

type of room	no.of night	extra bed	room price	complimentary	total

<tbody>
</tbody>

1. under number of night, there's a drop down list of : 1,2,3,4 ; for extra bed (drop down list as well): -,1,2 ; complimentary (drop down list): Y,N
2. room price is based on data from type of room : =if(A2="single", 100, if(A2="double",200,"")) (No issue here)
3. Lets take an example: type of room :single , no of night:2, extra bed :-, room price; 100, complimentary ; Y
Note: WHATEVER they choose, if the complimentary is "Y" then total will be "-", Also for each extra bed it will be +50 in the total later on
4. another example: type of room :single , no of night:2, extra bed :1, room price; 100, complimentary ; N
for the formula at "total" cell, this is what I input so far:=IF(E1="Y","-",IF(E5="N",B5*F5,"-"))+IF(C5<=2,C5*50,"")

This is where my error comes in (#value ). Im suspecting is because of the calculation steps doesnt make sense.
Please any kind sir/ma'am help me notify my issue and advice me on a solution as well
much appreciate it ^^

Regards,

 

[Aryatect]

Hi goldenparis,

Welcome to the MrExcel Message Board, in your formula "-" if output of the first part and then mathematical operation is applied on it hence you are getting #VALUE error. You can use below with the Total column formatted as accounting so that 0 is shown as "-"

	A	B	C	D	E	F
1	type of room	no.of night	extra bed	room price	complimentary	total
2	Single	1		50	Y	$ -
3	Double	2	1	100	Y	$ 50.00
4	Single	3		50	N	$ 150.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula F2 =IF(E2="N",D2*B2,0)+C2*50 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[goldenparis]

Hi goldenparis,

Welcome to the MrExcel Message Board, in your formula "-" if output of the first part and then mathematical operation is applied on it hence you are getting #VALUE error. You can use below with the Total column formatted as accounting so that 0 is shown as "-"

	A	B	C	D	E	F
1	type of room	no.of night	extra bed	room price	complimentary	total
2	Single	1		50	Y	$ -
3	Double	2	1	100	Y	$ 50.00
4	Single	3		50	N	$ 150.00

<tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula F2 =IF(E2="N",D2*B2,0)+C2*50 <tbody> </tbody>

<tbody>
</tbody>

Hey Aryatect, thanks for the guidance. There's a quick remark that I would like to make: for the F2 formula that you provided does solve the problem. Thank you very much. The question I have is that IF the complimentary is "Y" then the extra bed cost will be waived as well ie: "-"
Your formula helps solve the reasoning of no.of night, room price and complimentary, given that extra bed = 0. However for situation 2, when extra bed is involve and complimentary is "Y", total should be "-" as well, not $50.

Therefore, to solve my problem, I added and IF function and have your function as true value,
ie: =IF(E2="N",(IF(E2="N",D2*B2,0)+C2*50),"-")
IT WORKS~!!!! hahaha

Once again, thank you for your guidance^^

Discussion purpose:
Is the final formula considered nested if function? Also If I were to apply the same situation using VBA, is it possible for it?
I'm quite new to VBA as I just started learning about declaration and variables.

Thanks alot Aryatect. Happy learning from you.

Regards,
goldenparis

 

[Aryatect]

Glad to know the the problem is solved. Yeah this is an example of nested IF, in VBA (though I am a newbie there) it can be solved by nested IF or multiple condition in IF-ELSEIF too.