ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

2 Q & A posts tagged with CHOOSE

Fri
Apr 2
2010

How do i set up a formula in excel using "choose"?

i've got this for example : "11.Jul.95" and need to use a formula ( which i guess is "choose") to pick the season like July - Summer
please helpp!!! :D


Answer

Actually it is like this
If your date is in cell B1, then in C1 paste this

=TEXT( B1,"mmmm")&" - "&CHOOSE( MONTH( B1),"Winter","Winter", "Spring","Spring","Spring", "Summer", "Summer","Summer","Fall","Fall","Fall", "Winter")

This will show it exactly how you want it

VBAXLMan

Friday 4/2/2010 12:56:16 PM
Wed
Jan 21
2009

Excel Formula - I need help!?

Hi,

I'm not sure if I'm supposed to use the If formula for this - and if so, how. Here's what I want to do:

Something has X views, 10,000 for this example. I want:

1) The first 100 views to be worth 3 points each (=300 points)
2) The second 100 views to be worth 2 points each (=200 points)
3) The third 100 views to be worth 1 point each (=100 points)
4) Any view thereafter to be worth 0.75 of a point (in this case, 9,700 views times 0.75 = 7,275) points.

How would I create a formula like that (without having to actually divide up the views into four groups and do the separate calculations?)

Thanks!


Answer

So these views are in column B I assume starting from B1
Then in C1, paste this
=CHOOSE( INT( ROW()/100)+1,3,2,1,0.75)

And copy it down to the end of your list

This column will give you the points you have to multiply by, just add another column multiplying the value you want time this, or change the C1 into
=CHOOSE( INT( ROW()/100)+1,3,2,1,0.75)*B1

VBAXLMan is back

Wednesday 1/21/2009 2:42:33 PM

Tags

History