Execute different equations based on the value of a single cell

[s86design]

Hi,

New to the forum. Did a quick search for other threads on my topic but couldn't find anything so apologies if this is a duplicate question.

I am trying to insert specific values to complete an equation based on the value of a singe cell. I have attached images to aid my explanation.

For example in the images attached you will see that I have three columns I am pulling information from and multiplying by specific values to get my end total.

The values that I am using to multiply by, in this case 500, 400 & 600 the low end spectrum of my market.

What i would like to do is create an additional column with say a value of 1,2 or 3 (corresponding to the Low, Medium and High levels of my market) that would automatically insert different multiplication values for the end total.

For example. If I were to enter a 2 in the additional column, instead of multiplying by 400, 500 nd 600, it would multiply by 450, 550 and 650 ect...

Any help would be appreciated.

excel 2.jpg

excel 1.jpg

[windy58]

Not sure if this is what you want....

depending on how many different values you are multiplying by you could try using the IF function, if you have more than a couple then a LOOKUP of some sort.

First part of your existing formula would start: =IF(S105=2,O105*450,O105*400) You can then add subsequent IF statements.

Using a VLOOKUP it would start: =IFERROR(O105*VLOOKUP(S105,$T$105:$U$107,2,FALSE),"")

S105 is the additional column you mentioned, range T105:U107 is the lookup table.

A sample workbook is better to work from than images.

Windy

[Special-K]

Create a table in Sheet2 of the different bands and multipliers,e.g. 3 rows 4 columns

1 500 400 600 Low
2 450 550 650 Medium
3 700 800 900 High

Then use VLOOKUP based on the Low Medium High band (presumably 1,2,3) looking at that table and multiplying by the relevant row.
Assume Rooms and Pricing value are on Sheet1 Row 2 with the E1 is the band 1 2 or 3.

in Sheet1!D2
=VLOOKUP(E1,Sheet2!A1:D3,2,0)*A2+VLOOKUP(E1,Sheet2!A1:D3,3,0)*B2+VLOOKUP(E1,Sheet2!A1:D3,4,0)*C2

[s86design]

Hi,

Thank you both for you replies. I chose to go with Special K's method because it seemed a bit more straight forward.



Special K,

I believe I have followed your directions correctly however, I am still getting an error for the final value. I can not post my sample work book so I've had to use images again to show what I've done.

Sheet 1
excel 1.jpg


Sheet 2
excel 2.jpg

Thank you for your help. Much appreciated.

Frank

[windy58]

As per your original post all the cells were multiplied: O105*500 P105*400 & Q105*600
The vlookup formula is doing the same, only looking for what to multiply by first.
If 1 is placed in E1 then it return A2*500, B2*400 & C2*600 =2300, change the number in E1 to 2 then it return A2*450, B2*550 & C2*650 =2800
Windy

[s86design]

Hi Windy,

The main problem is that no number appears. When I enter the vlookup code into the cell it returns an NA value. I only clicked on the cell to show the code.

Thanks,

Frank

[windy58]

Use the IFERROR function which you can specify what will be returned if no match found.

For example:
=IFERROR(VLOOKUP(E1,Sheet2!A1:D3,2,0)*A2+VLOOKUP(E1,Sheet2!A1:D3,3,0)*B2+VLOOKUP(E1,Sheet2!A1:D3,4,0)*C2,"")

This will return a blank if no match found.

Change the "" at the end to what you want returned example "No match"

Windy