excel If Function containing logical test from multiple dropdown list

goldenparis

New Member
Joined
Jul 5, 2019
Messages
2
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 roomno.of nightextra bedroom pricecomplimentarytotal

<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
Joined
Jun 17, 2019
Messages
308
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

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

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

goldenparis

New Member
Joined
Jul 5, 2019
Messages
2
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
Joined
Jun 17, 2019
Messages
308
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top