excel formula

peggiegreg
peggiegreg used Ask the Experts™
on
hi experts,

I am looking at making a spread sheet that will allow me to put into a cell a letter between 'a' and 'f' and it will return a value of my choice.

what formula is used for this, and can anyone tell me step by step how to do this. I would google it but I cant seem to get my terminology correct.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
>>I am looking at making a spread sheet that will allow me to put into a cell a letter between 'a' and 'f' and it will return a value of my choice.

what kind of value to be returned from your choice? can you be more specific in this?

like, if "a" was selected, what value to be returned? into another cell, etc?

Author

Commented:
so for example, if a is selected I want it to say £1,500 and if you select b I want it to say £1700 etc... the values I can change once I have the formula in place.

then after that I wanted to be able to put in a postcode and for it to provide you with a letter for example if I entered SG2 9XR then it would work out what the first 2 letters were 'SG' combined with whatever number is entered in this example '2' and it will give you a letter.
Rob HensonFinance Analyst

Commented:
Set up a small table with required values:

      A      B
2      a      1500
3      b      1700
4      c      1900
5      d      2100
6      e      2300
7      f      2500

The uppercase letters and single digit numbers represent column and row headers, ie a range A2 to B7

Then use a lookup formula:

=VLOOKUP(C3,$A$2:$B$7,2,false)

That will find the value in C3 from list starting at A2 and return value from list starting in B2.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

How does "a is selected" relate to £1,500?
Similarly, what is the connection between "select b" and £1700?

Do you mean possible values in a lookup table could be, for example...

A 1500
B 1700
C 1900
D 2150
E 2795

?

Also, once the first two characters of "SG2 9XR" have been extracted, & combined with, I presume, a user-specified value of "2", what do you mean by "it will give you a letter"?

Is this also a lookup table, matching "SG" + "2" with a distinct value?

SG1 North Stevenage including Old Town & Town Centre
SG2 South Stevenage, Walkern, Ardeley
SG3 Knebworth, Datchworth, Woolmer Green
(& so on)

Also see: [ https://en.wikipedia.org/wiki/SG_postcode_area#coverage ]

Author

Commented:
thanks I will try what has been suggested so far and the reason for the request is I work for an insurance broker, one scheme we offer to customers has to be manually worked out and I wanted to try and automate it slightly.

so the first thing we do is use there postcode to determine a letter so for example the guide says SG 1-3 is cat 7 and SG 4-6 is cat 4.

the Cat changes the base rate we work from so if the postcode is cat 4 then it will start with a base rate off £XXXX.XX

what I am trying to achieve is someone can pop this postcode in and it will tell you the base rate.

hope this makes sense
Yes, that is a little clearer.  Thanks :)

OK, so it sounds like you could implement a "lookup table" for the base rate associated with a set of categories.

You also need some way of determining the category associated with a postcode.  That can also be a "lookup table".

Please see a very simple example within the attached workbook.
Q-28705234.xls

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial