ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

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

1 Q & A posts tagged with calculation

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