Formula

scouterbsa

New Member
Joined
Oct 23, 2002
Messages
10
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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
Joined
Oct 17, 2002
Messages
4
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
Joined
Mar 12, 2002
Messages
11,454
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
Joined
Oct 23, 2002
Messages
10
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
Joined
Mar 12, 2002
Messages
11,454
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
 

Forum statistics

Threads
1,144,438
Messages
5,724,361
Members
422,546
Latest member
KevinOnTheDaily

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top