Excel Help - "IF" function

I'm working on a fairly basic spreadsheet to work out prices for given services.

I have a drop down menu in on box containing 4 services ; Each one has a set up cost and monthly charge (all listed on the sheet)

What I want to do is select one of the services, then for the cell marked "Set Up" to drop in the set up cost for that service, and like wise for the monthly charge.

I can make it hapen for one service, but I can't work out how to make it do all 4....

Can anybody help ???

.

It goes something like this:

IIF(services = 'Service1',£10,IIF(services = 'Service2',£20,IIF(services = 'Service3',£30,IIF(services = 'Service4',£40,''))))

You are using a whole lot of nested 'IF', 'THEN', 'ELSE' statements. PM me or look up IIF function on Excel help...

OK, where your drop-down cell is A1 and your array containing the prices for each service is B1:C4 then here is your solution;

=VLOOKUP($A$1,$B$1:$C$4,2,0)

Use this formula in the cell in which you want to display the cost relative to the service shown in cell A1.

For robustness, use the array B1:B4 as your reference for the Data Validation in cell A1.

Hope this helps!

Scooby Drew - IIF() is an MS Access function!

Originally Posted by philh

Scooby Drew - IIF() is an MS Access function!

I forget, cos I work in Access all the time

Tell me about it. Why cant MS use more similar syntax across apps? I'm busting a gut trying to remember MSSQL now after years in Oracle.

Hmmmm Still can't get that to work.....

I have built a ref table:

• service1 10 15
  service2 15 20
  service3 20 30
  service4 25 40
  
  (This covers I4:K7)

But it doesn't seem to want to know.....

Can I not do something like =I((E19="basic",J4) put the right thing here (E19="Professional",J5)

Edit...

Got it sussed....

=IF((E15="Basic"),D6,IF((E15="Professional"),D7,IF ((E15="Premium"),D8,IF((E15="Customised"),D9,""))) )

.

Cool, glad you sussed it!

So, where does the array I4:K7 faeture in your solution?

Also, you have parenthesised the first argument in each of your nested IF()'s. This is the logical test and only needs parentesises if it is a calculation itself.

Pedantic little **** aren't I.

You've got it working so well done. I used to work as an Excel Developer and also did training for a bit. It's surprising how quickly most people give up on Excel when formulas start to get lengthy! 'Tis all simple when you know how.

Originally Posted by philh

Cool, glad you sussed it!

So, where does the array I4:K7 faeture in your solution?

Also, you have parenthesised the first argument in each of your nested IF()'s. This is the logical test and only needs parentesises if it is a calculation itself.

Pedantic little **** aren't I.

You've got it working so well done. I used to work as an Excel Developer and also did training for a bit. It's surprising how quickly most people give up on Excel when formulas start to get lengthy! 'Tis all simple when you know how.

Well I had the information on the page, but I dispensed with the idea of it being an array and just used it as reference (cell x = cell y) type of thing.

Next bit to do is the user costs, we have price breaks 1-10 11-25 etc so I need to do the same sort of thing, but with <or> ??) arguments.......

.