# Formula

#### scouterbsa

##### New Member
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%)

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### IML

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

Replies
6
Views
255
Replies
3
Views
1K
Replies
3
Views
275
Replies
3
Views
751
Replies
2
Views
881

1,181,924
Messages
5,932,811
Members
436,863
Latest member
ajlauten

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back