Check out some similar questions!

Daedalus · Nov 13, 2007, 12:11 AM

I want to do this problem in 2 cells.

One cell I have the value 769.15 and the other the formula (answer)

From the 769.15 the 1st 100 I take 2.20 and the next 100 and on 0.60.. The answer must be 6,40. As the 2,20 + (7*0.60) = 6,40.

Any ideas please

colbtech · Nov 13, 2007, 02:53 AM

Sorry, don't undertsand what you are trying to achieve. Must be having a thick day! Can you explain more?

Daedalus · Nov 13, 2007, 03:15 AM

OK here it goes...

100 = 2.20
100 = 0.60
100 = 0.60
100 = 0.60
100 = 0.60
100 = 0.60
100 = 0.60
69.15 = 0.60

So in otherwords the first 100 gives me 2.20 for the 100. Every 100 after that gives me 0.60 per 100.. If I take that all together it should give me 6:40.

I tried this formula gives me 3,20

+(sum(a1>100*0.60/100)))

colbtech · Nov 13, 2007, 05:43 AM

If you select 2.20 and all the 0.6 and the next cell below, click on autosum and you end up with 6.40.

I'm a little confused by your formula, what do you think the formula is doing? What value is in Cell A1?

Daedalus · Nov 13, 2007, 06:09 AM

Yes I know... ok I want to determain rates on values... now that value changes every time form user to user... the 769.15 I mean... there is over 1000 users... so sometimes the amount can be 3000 or even up to 10000. I don't want to put in the excel spreadsheat something like this

100
100
100
100
100
100
100
100
100
100
100
100

I want it aoutomatically... ONLY 2 CELLS one with the amoount ex 769.15 and next to it the answer... there must be an easier way than going through 1000 users and calculating it manually.

ScottGem · Nov 13, 2007, 07:22 AM

this formula should do it:

=2.2+(INT((A1-100)/100)+1)*0.6

Juat copy that formula down your column.

colbtech · Nov 13, 2007, 07:27 AM

Have a look at the attached file. See if it does what you want. To modify just add as many users as you want and then change the summing references in Cell F7.

One thing the calculations don't take into account multiple cells with less than 100.

colbtech · Nov 13, 2007, 07:28 AM

Good one Scott, beat me to it.

ScottGem · Nov 13, 2007, 07:31 AM

Originally Posted by colbtech

Good one Scott, beat me to it.

I'm assuming that he has a number in one cell and needs to figure out a value for that number based on 2.2 for the first 100 and .6 for each 100 or part thereafter. Not sure what happens if the number is less than 100 though.