Formulas with percentages

[Trisha V via OfficeKB.com]

maybe someone can help me with this..if someone increases an amount by %25
then they get a point, if they increase it by %50 they get 2 points, by %75,
3 points, and %100, 4 points.... can you figure that one out for me please?

how would i a formula that would read this?

 

[Govind]

Hi,

Lets say the original amount is in A2 and the increased amount is in A3
, then use

=ROUND((((A3-A2)*100)/A2)/25,0)

Regards

Govind.

 

[Trisha V via OfficeKB.com]

ok and if I already have a column that calculates for example A3 and A2?

 

[Govind]

Hi,

If you already have a column that calculates the variance in % between
A3 and A2 then just use

=ROUND(A4/25,0) where A4 is the cell with the increase % between A2 and A3.

This formula rounds off to the nearest point. If the increase is say
70%, it would give 3 points (rounded off to 75%).

If you dont want any rounding off ,you can just do simply =A4/25.

Govind.

If you want only

 

[Trisha V via OfficeKB.com]

ok that somewhat worked...if I needed the negative numbers to be 0?
and also They cant go over 100%? I think Im confusing both of us...lol

Hi,

If you already have a column that calculates the variance in % between
A3 and A2 then just use

=ROUND(A4/25,0) where A4 is the cell with the increase % between A2 and A3.

This formula rounds off to the nearest point. If the increase is say
70%, it would give 3 points (rounded off to 75%).

If you dont want any rounding off ,you can just do simply =A4/25.

Govind.

If you want only

ok and if I already have a column that calculates for example A3 and A2?

[quoted text clipped - 14 lines]

 

[Govind]

Hi,

Use

=IF(A4<0,0,MIN(4,ROUND(A4/25,0)))

where A4 is the % increase between A2 and A3. If you dont want any
rounding off of points, use

=IF(A4<0,0,MIN(4,A4/25))

Regards

Govind.

ok that somewhat worked...if I needed the negative numbers to be 0?
and also They cant go over 100%? I think Im confusing both of us...lol

Hi,

If you already have a column that calculates the variance in % between
A3 and A2 then just use

=ROUND(A4/25,0) where A4 is the cell with the increase % between A2 and A3.

This formula rounds off to the nearest point. If the increase is say
70%, it would give 3 points (rounded off to 75%).

If you dont want any rounding off ,you can just do simply =A4/25.

Govind.

If you want only

ok and if I already have a column that calculates for example A3 and A2?

[quoted text clipped - 14 lines]

how would i a formula that would read this?