Perplexed

[SCVic]

Look at the image and explain why adding the word "off" screw up the calculations.

 

[VoG]

You have posted an image which is unhelpful. Try using Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[kpark91]

It is probably because it recognizes "50% Off" or whatever as text.
You might want to add some formulas that convert them to numbers.

such as using Left() and Value()

 

[SCVic]

You have posted an image which is unhelpful. Try using Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Might I ask how the image is unhelpful? It describes the problem perfectly.

I have a drop-down utilizing a list of 5 text options.

The formula I posted in the ss works perfectly.

If I add the word "off" to the percentages, it causes the formula to error out.

I am not doing any calculations between a1:a5 and b1:b5.

Column A is the list of choices. Column B is the multiplier used to calculate cost per 1,000 units.

 

[SCVic]

It is probably because it recognizes "50% Off" or whatever as text.
You might want to add some formulas that convert them to numbers.

such as using Left() and Value()

I'm not doing any calc with the list of percentages and the column of decimals. The two columns of data are simply manually entered.

 

[Jonmo1]

Agree, image is unhelpfull.


You say the formula is 1000*(vlookup(index(a1:15,e5),a1:b5,2))
1. A1:15 is not a valid range, should be A1:A15
2. There is nothing in A1:A15
3. There is nothing in E5

I would think your formula could be much simplified...

=INDEX(B1:B5,E5)*1000

 

[SCVic]

Agree, image is unhelpfull.


You say the formula is 1000*(vlookup(index(a1:15,e5),a1:b5,2))
1. A1:15 is not a valid range, should be A1:A15
2. There is nothing in A1:A15
3. There is nothing in E5

I would think your formula could be much simplified...

=INDEX(B1:B5,E5)*1000

Yes, I know. That was a simple typo.

Corrected the typo for this discussion:

I need the vlookup in there to return the multiplier for the calc.

Also, I just tested your simplified formula and it does not work correctly.

F5 = 2, so the multiplier is .16. Your formula returned $200.00. Not correct.

 

[pagrender]

Does this work for you:
=VLOOKUP(E20,$B$15:$C$19,2,FALSE)*1000

or just the multiplier:
=VLOOKUP(E20,$B$15:$C$19,2,FALSE)

 

[Jonmo1]

Yep, overcomplicated....

Try
=INDEX(C1:C5,F5)*1000


The reason it doesn't work with Off added..

20% is a NUMBER
20% Off is a TEXT string.
When omitting the 4th argument in vlookup, it looks for a closest match
And the data must be sorted ascending, which your data is NOT.

Frankly surprised it worked without the Off...
Wierd results occur when using vlookup with "Closest Match" and Data not sorted ascending.


Hope that helps.

 

[VoG]

Might I ask how the image is unhelpful? It describes the problem perfectly.

If we copy that image into Excel, what do we get? A picture, nothing that we can work with. If you use Excel Jeanie we can copy from your shot and paste into Excel as data.