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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]type of room[/TD]
[TD]no.of night[/TD]
[TD]extra bed[/TD]
[TD]room price[/TD]
[TD]complimentary[/TD]
[TD]total[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
type of room	no.of night	extra bed	room price	complimentary	total
Single	Y
Double	Y
Single	N

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]

[TD="align: right"] $ - [/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"] $ 50.00 [/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]

[TD="align: right"] $ 150.00 [/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(E2="N",D2*B2,0)+C2*50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
type of room	no.of night	extra bed	room price	complimentary	total
Single	Y
Double	Y
Single	N

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]

[TD="align: right"] $ -[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"] $ 50.00[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]

[TD="align: right"] $ 150.00[/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]F2[/TH]
[TD="align: left"]=IF(E2="N",D2*B2,0)+C2*50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.