Create A Formula

[Brooke]

I would like to create a formula that would take multiple information and
return a letter Rank from A to E based on dollar revenue.

Example - I have Column A that contains a dollar amount. I would like cell
B to return a letter from A to E based on the following table -

A = $2500 and Up
B = $1000 - $2499
C = $250 - $999
D = $75 - $249
E = $0 - $74

So if Column A equals $1000 then it returns the letter B in that cell.

How do I write this formula or would I have to do each cell as an individual
formula. If (A2<$1000, "B"), If(A2<$75, "D")
Thanks
Brooke

 

[PhilD]

How do I write this formula or would I have to do each cell as an individual
formula. If (A2<$1000, "B"), If(A2<$75, "D")

You could "nest" your IF statements. So, you would have something
like:

=if(A2<75,"E",if(A2<250,"D",if(A2<1000,"C",if(A2<2500,"B","A"))))

There are probably other ways, too.

If A2 is less than 75 (i.e. 74 or under), return "E". Otherwise, if A2
is less than 250, return "D", and so on.

PhilD

 

[Brooke]

Perfect. I think that should work. Can I put a dollar range in the formula
though - Like If(A2 is between $1000 and $2499 return A). How would this be
done or would I have to create a pivot table.

Can you refer me to a a website or something that could explain pivot table
creation? I'm struggling with this new request. I have multiple customers
that are returned from a query and instead of doing subtotals and deleted
each duplicate cell I would like to have it total the customer and put it in
a seperate worksheet.
Thanks Again.

 

[CyberTaz]

Here is another option... but first, don't type any commas or currency
symbols into a function/formula - values only, such as 1000 or 73.95, etc.

Change the name "Revenue" (all the way through) to something else if you
like - but no spaces - then copy the following:

Public Function Revenue(dollars)
Select Case dollars
Case Is >= 2500
Revenue = "A"
Case Is > 999.99
Revenue = "B"
Case Is > 249.99
Revenue = "C"
Case Is > 74.99
Revenue = "D"
Case Else
Revenue = "E"
End Select
End Function


Then in your workbook go to Tools>Macro - Visual Basic Editor. In the VBA
editor window go to Insert>Module & paste the above user-defined function,
then close the VBA editor. Then you can use the Revenue function like any of
the built-in functions. If the values to be tested, for example are in cells
B10:B20 & you want the letter ranking in cells C10:C20, put the formula into
C10;

=Revenue(B10)

then copy down to C20.

 

[Brooke]

You are awesome. Can I do this with almost anything?? I guess I will have to
look into what Public Function and Case Is are all about?? This would make
alot of my reporting much easier.

Thanks Again.

 

[Bob Greenblatt]

I would like to create a formula that would take multiple information and
return a letter Rank from A to E based on dollar revenue.

Example - I have Column A that contains a dollar amount. I would like cell
B to return a letter from A to E based on the following table -

A = $2500 and Up
B = $1000 - $2499
C = $250 - $999
D = $75 - $249
E = $0 - $74

So if Column A equals $1000 then it returns the letter B in that cell.

How do I write this formula or would I have to do each cell as an individual
formula. If (A2<$1000, "B"), If(A2<$75, "D")
Thanks
Brooke

Brooke,

The easiest way to do this without any code is to create a lookup table.
Simply enter the maximum values in one column and the letter code in another
in descending order, like:

0 E
75 D
250 C
1000 B
25000 A

Then use the vlookup function. Assuming the table is in A1:B5, and your
questionable value is in A10, then a formula in B10 would look like:
=vlookup(A10,A1:B5,2)

 

[CyberTaz]

Glad it works for you!

In order to write your own functions you need some knowledge of VBA, but you
might want to look at the info here as a starting point:

http://office.microsoft.com/en-us/assistance/HA010548461033.aspx

Once you have a few basic concepts under your belt you can create just about
anything you need. If you stay organized as you go, you'll find that you can
often make revisions to what you've already written in order to create
variations on them. Not to mention that the folks who frequent this group
are phenominal - they'll be more than happy to help you along and will even
go so far as to proof your code and provide you with more elegant
alternatives. It's uncanny how willing they are to go to great lengths for
anyone who is making a reasonable efffort on their own.

--
Regards |:>)
Bob Jones
[MVP] Office:Mac

You are awesome. Can I do this with almost anything?? I guess I will have
to
look into what Public Function and Case Is are all about?? This would make
alot of my reporting much easier.

Thanks Again.