How to determine the number of units?

Delta = 1000;
Points_______Units
0___________1
1000________2 [0+1*Delta = 1000]
3000________3 [1000+2*Delta = 3000]
6000________4 [3000+3*Delta = 6000]
10000_______5 [6000+4*Delta = 10000]
15000_______6 [10000+5*Delta = 15000]
21000_______7 [15000+6*Delta = 21000]
28000_______8 [21000+7*Delta = 28000]

f(n) = Delta*[(n-1)+(n-2)+...+(n-n)]
where n = number of units, and f(n) = the minimum points to achieve the
number of units.

Does anyone know what function in excel to determine the number of units
based on certain point levels? such as if the points are 25600 in Cell A1,
then the number of units will be 7 in Cell B1?
Thank you for any suggestion
Eric

Hi!

Build a 2 column table like the one you've posted that lists the points in
the left column and the units in the right column.

Assume this table is in the range A6:B13 eith A5 and B5 being the headers
Points, Units.

A1 = 25600

B1 = formula:

=IF(A1="","",VLOOKUP(A1,A6:B13,2))

Biff

"Eric" <[email protected]> wrote in message
news:[email protected]...
> Delta = 1000;
> Points_______Units
> 0___________1
> 1000________2 [0+1*Delta = 1000]
> 3000________3 [1000+2*Delta = 3000]
> 6000________4 [3000+3*Delta = 6000]
> 10000_______5 [6000+4*Delta = 10000]
> 15000_______6 [10000+5*Delta = 15000]
> 21000_______7 [15000+6*Delta = 21000]
> 28000_______8 [21000+7*Delta = 28000]
>
> f(n) = Delta*[(n-1)+(n-2)+...+(n-n)]
> where n = number of units, and f(n) = the minimum points to achieve the
> number of units.
>
> Does anyone know what function in excel to determine the number of units
> based on certain point levels? such as if the points are 25600 in Cell A1,
> then the number of units will be 7 in Cell B1?
> Thank you for any suggestion
> Eric

Thank you for your suggestion, could it be possible using a formula to
determine the number of units?
Thank you
Eric

"Biff" wrote:

> Hi!
>
> Build a 2 column table like the one you've posted that lists the points in
> the left column and the units in the right column.
>
> Assume this table is in the range A6:B13 eith A5 and B5 being the headers
> Points, Units.
>
> A1 = 25600
>
> B1 = formula:
>
> =IF(A1="","",VLOOKUP(A1,A6:B13,2))
>
> Biff
>
> "Eric" <[email protected]> wrote in message
> news:[email protected]...
> > Delta = 1000;
> > Points_______Units
> > 0___________1
> > 1000________2 [0+1*Delta = 1000]
> > 3000________3 [1000+2*Delta = 3000]
> > 6000________4 [3000+3*Delta = 6000]
> > 10000_______5 [6000+4*Delta = 10000]
> > 15000_______6 [10000+5*Delta = 15000]
> > 21000_______7 [15000+6*Delta = 21000]
> > 28000_______8 [21000+7*Delta = 28000]
> >
> > f(n) = Delta*[(n-1)+(n-2)+...+(n-n)]
> > where n = number of units, and f(n) = the minimum points to achieve the
> > number of units.
> >
> > Does anyone know what function in excel to determine the number of units
> > based on certain point levels? such as if the points are 25600 in Cell A1,
> > then the number of units will be 7 in Cell B1?
> > Thank you for any suggestion
> > Eric
>
>
>

>> > Does anyone know what function in excel to determine the number of
>> > units
>> > based on certain point levels? such as if the points are 25600 in Cell
>> > A1,
>> > then the number of units will be 7?

Hi. It looks to me like your point level (P) sequence is based on your
units (U) with the following equation.

P = 1000*U*((U + 1)/2)

Therefore, your integer "Units" can be calculated with the following
equation based on points (P).

=CEILING((1/50)*(SQRT(5)*SQRT(P + 125) - 25),1)

When cell 'P' is 25600, the above returns 7.
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Eric" <[email protected]> wrote in message
news:[email protected]...
> Thank you for your suggestion, could it be possible using a formula to
> determine the number of units?
> Thank you
> Eric
>
> "Biff" wrote:
>
>> Hi!
>>
>> Build a 2 column table like the one you've posted that lists the points
>> in
>> the left column and the units in the right column.
>>
>> Assume this table is in the range A6:B13 eith A5 and B5 being the headers
>> Points, Units.
>>
>> A1 = 25600
>>
>> B1 = formula:
>>
>> =IF(A1="","",VLOOKUP(A1,A6:B13,2))
>>
>> Biff
>>
>> "Eric" <[email protected]> wrote in message
>> news:[email protected]...
>> > Delta = 1000;
>> > Points_______Units
>> > 0___________1
>> > 1000________2 [0+1*Delta = 1000]
>> > 3000________3 [1000+2*Delta = 3000]
>> > 6000________4 [3000+3*Delta = 6000]
>> > 10000_______5 [6000+4*Delta = 10000]
>> > 15000_______6 [10000+5*Delta = 15000]
>> > 21000_______7 [15000+6*Delta = 21000]
>> > 28000_______8 [21000+7*Delta = 28000]
>> >
>> > f(n) = Delta*[(n-1)+(n-2)+...+(n-n)]
>> > where n = number of units, and f(n) = the minimum points to achieve the
>> > number of units.
>> >
>> > Does anyone know what function in excel to determine the number of
>> > units
>> > based on certain point levels? such as if the points are 25600 in Cell
>> > A1,
>> > then the number of units will be 7 in Cell B1?
>> > Thank you for any suggestion
>> > Eric
>>
>>
>>

Oops! Let me change that sequence slightly...

P = 1000 * (U- 1)*U/ 2
or...
P = 500*(U-1)*U

Therefore, try this equation instead given points 'P'

=INT((25 + SQRT(5)*SQRT(125 + P))/50)

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003

<snip>

>>> > Points_______Units
>>> > 0___________1
>>> > 1000________2 [0+1*Delta = 1000]
>>> > 3000________3 [1000+2*Delta = 3000]
>>> > 6000________4 [3000+3*Delta = 6000]
>>> > 10000_______5 [6000+4*Delta = 10000]
>>> > 15000_______6 [10000+5*Delta = 15000]
>>> > 21000_______7 [15000+6*Delta = 21000]
>>> > 28000_______8 [21000+7*Delta = 28000]

Thank you very much
Eric

If the value of Delta changes, the following formula does not work.
U=INT((25 + SQRT(5)*SQRT(125 + P))/50) [Delta = 1000 {Default}]
Could you please give me any suggestion on how to fix the above formula
without cancelling the 1000 for delta?
I look forward to your reply
Thank you very much
Eric

"Dana DeLouis" wrote:

> Oops! Let me change that sequence slightly...
>
> P = 1000 * (U- 1)*U/ 2
> or...
> P = 500*(U-1)*U
>
> Therefore, try this equation instead given points 'P'
>
> =INT((25 + SQRT(5)*SQRT(125 + P))/50)
>
> --
> HTH. :>)
> Dana DeLouis
> Windows XP, Office 2003
>
> <snip>
>
> >>> > Points_______Units
> >>> > 0___________1
> >>> > 1000________2 [0+1*Delta = 1000]
> >>> > 3000________3 [1000+2*Delta = 3000]
> >>> > 6000________4 [3000+3*Delta = 6000]
> >>> > 10000_______5 [6000+4*Delta = 10000]
> >>> > 15000_______6 [10000+5*Delta = 15000]
> >>> > 21000_______7 [15000+6*Delta = 21000]
> >>> > 28000_______8 [21000+7*Delta = 28000]
>
>
>

See if this will work for you:
'd is Delta, and 'P is your points.

=INT((1 + SQRT(d + 8*P)/ SQRT(d))/2)

The returned value should be your integer 'Units.'
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Eric" <[email protected]> wrote in message
news:[email protected]...
> If the value of Delta changes, the following formula does not work.
> U=INT((25 + SQRT(5)*SQRT(125 + P))/50) [Delta = 1000 {Default}]
> Could you please give me any suggestion on how to fix the above formula
> without cancelling the 1000 for delta?
> I look forward to your reply
> Thank you very much
> Eric
>
> "Dana DeLouis" wrote:
>
>> Oops! Let me change that sequence slightly...
>>
>> P = 1000 * (U- 1)*U/ 2
>> or...
>> P = 500*(U-1)*U
>>
>> Therefore, try this equation instead given points 'P'
>>
>> =INT((25 + SQRT(5)*SQRT(125 + P))/50)
>>
>> --
>> HTH. :>)
>> Dana DeLouis
>> Windows XP, Office 2003
>>
>> <snip>
>>
>> >>> > Points_______Units
>> >>> > 0___________1
>> >>> > 1000________2 [0+1*Delta = 1000]
>> >>> > 3000________3 [1000+2*Delta = 3000]
>> >>> > 6000________4 [3000+3*Delta = 6000]
>> >>> > 10000_______5 [6000+4*Delta = 10000]
>> >>> > 15000_______6 [10000+5*Delta = 15000]
>> >>> > 21000_______7 [15000+6*Delta = 21000]
>> >>> > 28000_______8 [21000+7*Delta = 28000]
>>
>>
>>

> =INT((1 + SQRT(d + 8*P)/ SQRT(d))/2)

If we assume that 'D (Delta) is greater than zero, then I guess we can
shorten that equation just a little:

=INT((1 + SQRT(1 + (8*P)/D))/2)

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003

<Snip>