Formula

[scouterbsa]

I am trying to get something to work, as the scouts in the troop sell popcorn. The amount they sell goes in cell 1 (in dollars) I need a formula in cell 2 that will calculate a percentage of cell 1. As 0-500 (60%), 501-700 (70%), 701-800 (80%), 801-999 (90%) and over 1000 (100%)

 

[IML]

You could use a vlookup such as
=A1*VLOOKUP(A1,{0,0.6;501,0.7;701,0.8;801,0.9;1000,1},2)

or replace the {..} part with a cell reference to a table you can maintain.

 

[Buzz]

Assuming your data is in cell A1, type the following formula in cell A2:

=IF($A$1<501,0.6,IF($A$1<701,0.7,IF($A$1<801,0.8,IF($A$1<1000,0.9,1))))

Cell A2 should be formatted as percent.

 

[Yogi Anand]

Hi scouterbsa:

Welcome to the Board!

You can use the following VLOOKUP formula for this

=VLOOKUP(A1,{0,0.6;501,0.7;701,0.8;801,0.9;1000,1},2)

If your requirements are more comprehensive, you can setup a table where the range of values can be stored.

Regards!

Yogi Anand

 

[scouterbsa]

IML Thanks yours works except the answer is one decimal to high. I can fix that.
Buzz-I need the answer in dollars, not percent, Thanks
Yogi-Yours didn't work, but IML did. The A1* made the difference. Thanks

 

[Yogi Anand]

Hi scouterbsa:

I believe in your post you wanted to calculate the percentageage of the value in cell 1 (cell A1)

I am trying to get something to work, as the scouts in the troop sell popcorn. The amount they sell goes in cell 1 (in dollars) I need a formula in cell 2 that will calculate a percentage of cell 1. As 0-500 (60%), 501-700 (70%), 701-800 (80%), 801-999 (90%) and over 1000 (100%)

My formula gives you the percentage of value in cell 1 (cell A1). IML's formula gives you the computed dollars.

The good thing is that you got the answer you were looking for.

Regards!

Yogi Anand