# excel If Function containing logical test from multiple dropdown list

#### goldenparis

##### New Member
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,

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Aryatect

##### Active Member
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 "-"

ABCDEF
1type of roomno.of nightextra bedroom pricecomplimentarytotal
2Single150Y \$ -
3Double21100Y \$ 50.00
4Single350N \$ 150.00

</tbody>
Sheet8

Worksheet Formulas
CellFormula
F2=IF(E2="N",D2*B2,0)+C2*50

</tbody>

<tbody>
</tbody>

Last edited:

#### goldenparis

##### New Member
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 "-"

ABCDEF
1type of roomno.of nightextra bedroom pricecomplimentarytotal
2Single150Y \$ -
3Double21100Y \$ 50.00
4Single350N \$ 150.00

<tbody>
</tbody>
Sheet8

Worksheet Formulas
CellFormula
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

##### Active Member
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.

Replies
14
Views
175
Replies
1
Views
216
Replies
2
Views
258
Replies
3
Views
241
Replies
2
Views
57

1,129,555
Messages
5,637,020
Members
416,954
Latest member
Gohar hussain

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back