Formula for SS

[Kah-Thiam Tay]

What is the formula to use in the PCOST cell in a situation like this:

If COST is less than $1,000, then PCOST is $1
If COST is between $1001 and 1,999, then PCOST is $2
If COST is between $2,000 and 2,999, then PCOST is $3
If COST is between $3,000 and 3,999, then PCOST is $4
If COST is between $4,000 and 4,999, then PCOST is $5
If COST is between $5,000 and 5,999, then PCOST is $6

and so on, until between $9,000 and 9,999 range.

I am trying to help a friend and I know very little about formulas. Please help. Thanks.

Kah-Thiam Tay.

[Barry]

What is the formula to use in the PCOST cell in a situation like this:

If COST is less than $1,000, then PCOST is $1
If COST is between $1001 and 1,999, then PCOST is $2
If COST is between $2,000 and 2,999, then PCOST is $3
If COST is between $3,000 and 3,999, then PCOST is $4
If COST is between $4,000 and 4,999, then PCOST is $5
If COST is between $5,000 and 5,999, then PCOST is $6

and so on, until between $9,000 and 9,999 range.

I am trying to help a friend and I know very little about formulas. Please help. Thanks.

Kah-Thiam Tay.

Should the first three examples above read(change highlighted):

If COST is less than $1,000, then PCOST is $1
If COST is between $1000 and 1,999, then PCOST is $2
If COST is between $2,000 and 2,999, then PCOST is $3

That's the assumption made in the formulas below.


The easiest method for calculating PCOST is to divide COST by 1000 (which for COST = 4000 to 4999 will give a result between 4 and 4.999) then ust either INT() or TRUNC() to give the whole number part of that value (in this case, 4). If you want the $ sign added, set the number format of the cell to Currency and the precision to as many digits as you want following the decimal.

Formulas:

These assume COST is in cell A1.

=INT(A1/1000)

or

=TRUNC(A1/1000)

These are both general formulas, and will work well beyond the $9999 range.

Regards,
Barry

[haumann]

What is the formula to use in the PCOST cell in a situation like this:

If COST is less than $1,000, then PCOST is $1
If COST is between $1001 and 1,999, then PCOST is $2
If COST is between $2,000 and 2,999, then PCOST is $3
If COST is between $3,000 and 3,999, then PCOST is $4
If COST is between $4,000 and 4,999, then PCOST is $5
If COST is between $5,000 and 5,999, then PCOST is $6

and so on, until between $9,000 and 9,999 range.

I am trying to help a friend and I know very little about formulas. Please help. Thanks.

Kah-Thiam Tay.

::: snip :::
The easiest method for calculating PCOST is to divide COST by 1000 (which for COST = 4000 to 4999 will give a result between 4 and 4.999) then ust either INT() or TRUNC() to give the whole number part of that value (in this case, 4). If you want the $ sign added, set the number format of the cell to Currency and the precision to as many digits as you want following the decimal.

Formulas:

These assume COST is in cell A1.

=INT(A1/1000)

or

=TRUNC(A1/1000)

These are both general formulas, and will work well beyond the $9999 range.

Regards,
Barry

Barry, it would seem that the shoe is on the other foot, this time... ;-)

To get the desired results, the formulas need just one minor tweak:

=1 + INT(A1/1000)

or

=1 + TRUNC(A1/1000)

Otherwise, the PCOST value will be one dollar too low.



(Usually it's Barry who catches my "blunders".)

John@was

[Kah-Thiam Tay]

Thanks, guys, it worked. Always find this forum helpful.

[Kah-Thiam Tay]

Thanks, guys, it worked. Always find this forum helpful.

[Barry]

Barry, it would seem that the shoe is on the other foot, this time... ;-)

To get the desired results, the formulas need just one minor tweak:

=1 + INT(A1/1000)

or

=1 + TRUNC(A1/1000)

Otherwise, the PCOST value will be one dollar too low.



(Usually it's Barry who catches my "blunders".)

John@was

You're right, of course!

Too much concentrating on the 1001-1999 anomaly, and not enoug reading to the end of the line.

We make a good team, John!

Regards,
Barry