This can be done in a lot of ways depending on how your spreadsheet looks, how you are selecting the options and so on.
With nothing to go on, and no replies for further clarification, I'll just give you an example on how it COULD look.
(Note that my excel options uses semicolon instead of comma, for most people, the function code in this picture should be:
Let's say this is how it looks. Nevermind the fact that they are on the same worksheet, this can be easily changed.
What I've done here is used Data Validation to create a few drop-down lists to select service, length and quantity. And then added a formula to sum up the result of the selected options.
Here follows a short guide on how to do it:
First create the tables with the data. They don't have to be in tables.
Create drop-down lists with data validation.
Select the cell which should represent the level of service and go to the data tab and select data validation. Select "list" and then add the source.
Repeat the process for the other two selection boxes.
Note. For single column tables – like table 2 in this example – you could replace the source range with
Adding it all together.
This was the original question, and in this case it's simple to add them together.
The Idea is to just multiply the option field together like
But that obviously doesn't quite work in this case.
First of, B10 shows the name of the service, not the price. So we use
=VLOOKUP() to see what the price of the service is.
Syntax for VLOOKUP is
=VLOOKUP (value, table, col_index, [range_lookup])
The value to look up is in B10, the table we want to check is "Table1" and the column with the result we want is column 2 (If it isn't a table, using B3:C6 works just as well).
Now we have
=VLOOKUP(B10,Table1,2)*C10*D10 which works, but wait, we are missing something!
I use the same method with
VLOOKUP() to determine the discount of the quantity.
returns the value 0.02 in this case. But we can't multiply it all with 0.02, we want a 2% discount. So we add (1- before it, to invert the % and get 0.98.
Now we can add it to the function. And the result is:
I hope this example will help you on your way with your project. Good luck!
Oh, almost forgot. To refer to a range, table or cell on another worksheet, just add the worksheet name and ! in front of it. So B2 on Sheet2 would be