Excel question

[Nocturnal]

I have a cell and formula as follows:

=IF(C3="Premium",152.00,0.00)

However, I want it to be if C3="Premium" then 152.00 OR if C3="Advance" then 107.00.

How would I achieve this?

Ok I was able to achieve what I wanted by:

=IF(C3="Premium",152,IF(C3="Advance",107))

However, what if I want to add more things such as another "IF" statement?

How would this be achieved?

Like Premium=152, Advance=107, Install=45, Warranty=0, Data Backup=$45 etc etc etc?

 

[BlueWeasel]

It's not the most efficient way to do it, but just keep nesting IF statements together until you get all the parameters in. Your formula will be a mile long, but it should work.

 

[NeoV]

you could also make a table on another worksheet that has all your possible values, and then use a a vlookup...

for example....

on worksheet 2, Cell A1 is Premium, A2 is Advance, A3 is install, A4 is Warranty, A5 is Data Backup
on worksheet 2, Cell B1 is 152, B2 is 107, B3 is 45, B4 is 0 , B5 is 45

on worksheet 1, Cell A1 is your data entry point - personally I would use conditional formatting to make it a drop-down box, so there are no issues with items being mis-spelled
your formula in cell B1 would be =VLOOKUP(A1,Sheet2!$A$1:$B$5,2,FALSE)

I think that is easier than nesting all the if statements together - once you get past 3 or 4, just keeping track of the parentheses and comma's is a pain!

 

[Nocturnal]

I got it NeoV. However, is there anyway to make a certain field always be a default before me entering something? Reason being is if I don't have anything in the cell with the formula the cell says something like REF#.

 

[CrimsonChaos]

Using NeoV's proposed code, you could do something like this:

=VLOOKUP(IF(ISBLANK(A1),"Premium",A1),Sheet2!$A$1:$B$5,2,FALSE)

This will default to "Premium" if the A1 field is left blank, otherwise it will use the value in A1 (on Sheet1).

 

[PowerEngineer]

Originally posted by: CrimsonChaos
Using NeoV's proposed code, you could do something like this:

=VLOOKUP(IF(ISBLANK(A1),"Premium",A1),Sheet2!$A$1:$B$5,2,FALSE)

This will default to "Premium" if the A1 field is left blank, otherwise it will use the value in A1 (on Sheet1).

Or this: IF(ISERR(VLOOKUP(A1,Sheet2!$A$1:$B$5,2,FALSE)),"Premium",VLOOKUP(A1,Sheet2!$A$1
$B$5,2,FALSE)) which makes "premium" the default when A1 contains something that's not one of the valid choices.

 

[Nocturnal]

What if I want to make it default to blank?

 

[CrimsonChaos]

Mmmm, I'm not sure what you mean. If you mean that you just want the cell to be blank if your input value is blank/invalid, then replace the "Premium" defaults we provided with just empty quotes, like this ""

 

[NeoV]

if you want the default to be blank, just use "" instead of "premium"