if function

[SparkyNick]

Hi guys

I have been trying to get this to work for some time now and just cant work it out. What im wanting to do is this.

I am a tradesman and want to make my quotes easyer to do so have tried to make a worksheet to do this.

For example: I have a set price for installing a power point ( $120 ) and for 2 power points ( $200 ) and for 3 ( $250 ) and so on until 5 or more is just $80each. I have worked out how to do this even if it is not the right way to do it

=IF(B5=1,"100")+IF(B5=2,"200")+IF(B5=3,"250")+IF(B5=4,"330")+IF(B5=5,"380")+IF(B5>=5,(80*B5))

What im trieng to do now is I have a list of items with option buttons to choose from to select if the house is brick or weatherboard or 2 storey or has a tin roof as these make the job harder so we need to charge more. I came up with this to add 10% to the existing formula but there are 5 conditions I need this to work for.

=IF(BUILDINGTYPE!G9=1,110%*(+IF(B5=1,"110")+IF(B5=2,"200")+IF(B5=3,"250")+IF(B5=4,"330")+IF(B5=5,"380")+IF(B5>=5,(80*B5))),(+IF(B5=1,"110")+IF(B5=2,"200")+IF(B5=3,"250")+IF(B5=4,"330")+IF(B5=5,"380")+IF(B5>=5,(80*B5))))

So if condition is true times the result value by 110% if false just show result.

So the next line would need to say

(BUILDINGTYPE!G9=2,120%

THEN

(BUILDINGTYPE!G9=3,130% and so on

when I do it, it does the 110% but adds the 120% to it as well instead of just 120%... I may also need to minus some as well.

I have tried to use the 'OR' function but cant get it to work

Any help would be great as im only teaching myself how to use excel and with no training at all its bloody hard

Cheers

Nick

[Ron Coderre]

Well, you've decided to begin with a somewhat complicated application. That's a good thing. Why? Because it will punish you for every inefficiency you try to use...forcing you to develop good spreadsheet habits.

I recommend that you begin by studying some of the examples at Debra Dalgleish's website for ideas and instructions.

Start here:
http://www.contextures.com/xlOrderForm01.html

Post back if you have more questions.

[daddylonglegs]

Hello Nick,

Just a couple of suggestions...

When you want a formula to return a number don't use quotes, that makes the "number" into text and could cause you problems with subsequent calculations....

You could use a CHOOSE function to replace your first formula, i.e.

=CHOOSE(MIN(6,B5),100,200,250,330,380,80*B5)

[SparkyNick]

Thankyou guys

Ok, it would seem I have just pulled the lid off a very big can of worms

The way I was doing it was going to be very messy when finished so after reading Rons reply I had a look at that website and found a few things to get me started.

Also the formula you posted Daddylonglegs just shows how much I have to learne as that works so well and is less confusing to read.

Now

What I think im going to do now is have a list of "items" on the top row titled ie power points, lighting, switchboards, new home, labour, extras and so on.

in each row I will put in a list of items required under each heading for a drop down list of options. I have worked out how to do this from Rons link but my question now is

1) =IF(A10="DGPO",CHOOSE(MIN(6,B10),100,200,250,330,380,80*B10))

this is what I have used to get the price to come up when "DGPO" is selected but if the list has 20+ items in it how many of them can I add to this formula. so the price will change for the new selected item..

I know this is wrong but to maybe explain what I mean.

=IF(A10="DGPO",CHOOSE(MIN(6,B10),100,200,250,330,380,80*B10)) OR
(IF(A10="ip53DGPO",CHOOSE(MIN(6,B10),160,280,360,450,520,110*B10)) OR (IF(A10="DGPO/EX/SW",CHOOSE(MIN(6,B10),100,200,250,330,380,80*B10))

Hope you see what I am trying to do.

cheers

Nick