Excel Fomula for %

Andree · Apr 21, 2011

Hi,

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CALAUDE%7E2%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CALAUDE%7E2%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CALAUDE%7E2%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style> <table class="MsoNormalTable" style="width: 192pt; margin-left: 4.8pt; border-collapse: collapse;" width="256" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15pt;"> <td style="width: 48pt; border: 1pt solid windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="64" nowrap="nowrap"> [FONT=&quot]A<o

></o

>[/FONT]
</td> <td style="width: 48pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="64" nowrap="nowrap"> [FONT=&quot]B<o

></o

>[/FONT]
</td> <td style="width: 48pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="64" nowrap="nowrap"> [FONT=&quot]C<o

></o

>[/FONT]
</td> <td style="width: 48pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="64" nowrap="nowrap"> [FONT=&quot]D<o

></o

>[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="64" nowrap="nowrap"> [FONT=&quot]AB<o

></o

>[/FONT]
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="64" nowrap="nowrap"> [FONT=&quot]BC<o

></o

>[/FONT]
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="64" nowrap="nowrap"> [FONT=&quot]DE<o

></o

>[/FONT]
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="64" nowrap="nowrap"> [FONT=&quot]FI<o

></o

>[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="64" nowrap="nowrap"> [FONT=&quot]FI<o

></o

>[/FONT]
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="64" nowrap="nowrap"> [FONT=&quot]DE<o

></o

>[/FONT]
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="64" nowrap="nowrap"> [FONT=&quot]BC<o

></o

>[/FONT]
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="64" nowrap="nowrap"> [FONT=&quot]AB<o

></o

>[/FONT]
</td> </tr> </tbody></table>

In the above example I need a formula in column E. Which should assign %s to the data in cells A, B, C, D.

Example if it is AB in column A then should give 10% or if its BC then it should throw 20%, DE - 30% and FI - 40%.

Please help me with the formula...

jasonb75 · Apr 21, 2011

Try

Excel Workbook

A

B

C

D

1

DE

30%

AB

10%

2

BC

20%

3

DE

30%

4

FI

40%

Sheet

JazzSP8 · Apr 21, 2011

Or you could try;

Code:

=LOOKUP(A1,{"AB","BC","DE",",FI";"10%","20%","30%","40%"})

Andree · Apr 21, 2011

Thanks for this….but there is one more bit of modification to the data set I realized now. The Cell B1 should throw "30%" even if Cell A1 has "D" in it rather than the whole of "DE".

jasonb75 · Apr 21, 2011

JazzSP8 said:
Or you could try;

Code:

=LOOKUP(A1,{"AB","BC","DE",",FI";"10%","20%","30%","40%"})

But you're returning numbers as text which can be a problem if the result is being passed to another formula, depending on the functions used.

jasonb75 · Apr 21, 2011

Andree said:
Thanks for this….but there is one more bit of modification to the data set I realized now. The Cell B1 should throw "30%" even if Cell A1 has "D" in it rather than the whole of "DE".

Is that only for D / DE or should it be A B D or F?

JazzSP8 · Apr 21, 2011

jasonb75 said:
But you're returning numbers as text which can be a problem if the result is being passed to another formula, depending on the functions used.

Fair comment, but I use the same formula for something similar with no issue

If it is an issue then you can simply wrap it in a a VALUE anyways;

Code:

=VALUE(LOOKUP(A1,{"AB","BC","DE",",FI";"10%","20%","30%","40%"}))

Andree said:
Thanks for this….but there is one more bit of modification to the data set I realized now. The Cell B1 should throw "30%" even if Cell A1 has "D" in it rather than the whole of "DE".

Is it only the first character that matters in all cases?

jasonb75 · Apr 21, 2011

JazzSP8 said:
Fair comment, but I use the same formula for something similar with no issue

If it is an issue then you can simply wrap it in a a VALUE anyways;

Code:

=VALUE(LOOKUP(A1,{"AB","BC","DE",",FI";"10%","20%","30%","40%"}))

Or just use the double unary method

=--LOOKUP(.....)

Personally with your method I would go with

=--(VLOOKUP(A2,{"AB",10;"BC",20;"DE",30;"FI",40},2,0)&"%")

Prevents any 'erratic' codes from returning a result

JazzSP8 · Apr 21, 2011

jasonb75 said:
Or just use the double unary method

=--LOOKUP(.....)

Personally with your method I would go with

=--(VLOOKUP(A2,{"AB",10;"BC",20;"DE",30;"FI",40},2,0)&"%")

Prevents any 'erratic' codes from returning a result

Yup .. Many many ways to skin a cat in Excel

Andree · Apr 21, 2011

jasonb75, its not only for DE but even for FI, BC etc.....

Excel Fomula for %

Andree

New Member

Excel Facts

jasonb75

Well-known Member

JazzSP8

Well-known Member

Andree

New Member

jasonb75

Well-known Member

jasonb75

Well-known Member

JazzSP8

Well-known Member

jasonb75

Well-known Member

JazzSP8

Well-known Member

Andree

New Member

Similar threads

Forum statistics

Share this page

Excel Fomula for %

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock