MrExcel Publishing
Your One Stop for Excel Tips & Solutions

See if you can help me solve this one!!!


Posted by Bis on April 09, 2001 9:20 AM

I have a chart similiar to this:


PERCENTS(Y) AMOUNTS (X)
0-2000 2001-6000 6001-8000
1 0 - 40% 100 400 700
2 40 - 50% 200 500 800
3 50 - 60% 300 600 900

ENTER AMOUNT: ???
ENTER PERCENT: ???

TOTAL EQUALS:???

My problem is this:
I want the user to enter an amount (X) and a percent (Y). Then I want the cell where these two meet to be retrieved.

For example: If I entered the amount 2500, and the percent 42%, I want to receive "TOTAL EQUALS: 500"

Please let me know how I can do this. I'm sure there's a way.

Regards,
Bismarck


Posted by Aladin Akyurek on April 09, 2001 10:11 AM


:Bismarck

I don't get it: Given 2500 as the amount and 42% as the percent, what does 500 mean? In other words, how is that number composed, given the amount and the percent?

Aladin

Posted by Bis on April 09, 2001 10:19 AM

The number was simply symbolic. My point is that being given a column heading and a row heading, I need to find where those two meet. That one cell must then be returned to me.

Posted by Aladin Akyurek on April 09, 2001 10:31 AM

Well, in that case, care to provide a snippet of your real data if possible? Here is how: Activate an empty cell, enter =, select the relevant but a small portion of data, then hit control+shift+enter at the same time. You'll see at the formula bar a range put between 2 braces {}. Now: select the range in the formula and hit F9. Copy the resulting array and paste it into your post.

Aladin

Posted by Bis on April 09, 2001 10:35 AM

{" Mark-up","< $960,000","$960,001 - $1,199,999","$1,200,000 <";"4 % or Under",0.0008,0.001,0.0012;"4.01% - 6%",0.0009,0.0012,0.0014;"Over 6.01%",0.001,0.0014,0.0017}

Let me know if this helps, or if you need something else.

Thanks,
Bis

Posted by Azli on April 09, 2001 10:58 AM

Regards,

Try this

Assuming B2:D4 is where the numbers are, and B7 is where you put the Percent and B6 is where you put the Amount

=INDEX(B2:D4,IF(B7<40,1,IF(B7<50,2,IF(B7<=60,3))),IF(B6<2000,1,IF(B6<6000,2,IF(B6<=8000,3))))

This'll work for a small range but for a bigger data set you might want to use a LOOKUP function, let me know if this is the case.

Posted by Azli on April 09, 2001 11:19 AM

VLOOKUP

Try this. Remember, I didn't include for the headings PERCENTS and AMOUNTS so, you may have to change the range in the formula.
I assume the A2 is "0 - 40%" and B2 is "40-50%" and C2 is "50-60%".
Again, B7 is where you put the Percent [e.g 23] and B6 is where you put the Amount [e.g. 1500].

=VLOOKUP(IF(B7<40,A2,IF(B7<50,A3,IF(B7<=60,A4))),A2:D4,IF(B6<2001,2,IF(B6<6001,3,IF(B6<=8000,4))))

Posted by Aladin Akyurek on April 09, 2001 11:27 AM


====================

Bis

I propose a small reorganization of your table of data as follows:

{0,0,960000,1200000;0,0.0008,0.001,0.0012;0.04,0.0009,0.0012,0.0014;0.06,0.001,0.0014,0.0017}

where he top row consists of

B1 $0.0
C1 $960,000
D1 $1,200,000

and the first column

A2 0%
A3 4%
A4 6%

I assumed that this table of data occupies thus the range A1:D4 (A1 is empty).

Select the range B1:D1 and name it AMOUNTS via the Name Box.
Select the range A2:D4 and name it TOTALS via the Name Box.

In A9 enter: ENTER AMOUNT:
Name the cell B9 "Amount" via the Name Box.

In A10 enter: ENTER PERCENT:
Name B10 "Percent" and format this cell by means of the %-icon.

In A11 enter: TOTAL EQUALS:
In B11 enter: =VLOOKUP(Percent,TOTALS,LOOKUP(Amount,AMOUNTS,1)+1,1)

Note that if you didn't or couldn't name the ranges and cells as I indicated, then use the actual references in the formula of B11.

Note also that LOOKUP in the formula determines the column of AMOUNTS where to look for a total given a percent.

Aladin

Posted by Aladin Akyurek on April 09, 2001 11:45 AM

Correction...

==================== Bis I propose a small reorganization of your table of data as follows: {0,0,960000,1200000;0,0.0008,0.001,0.0012;0.04,0.0009,0.0012,0.0014;0.06,0.001,0.0014,0.0017} where he top row consists of B1 $0.0 C1 $960,000 D1 $1,200,000 and the first column A2 0% A3 4% A4 6% I assumed that this table of data occupies thus the range A1:D4 (A1 is empty). Select the range B1:D1 and name it AMOUNTS via the Name Box. Select the range A2:D4 and name it TOTALS via the Name Box. In A9 enter: ENTER AMOUNT: Name the cell B9 "Amount" via the Name Box. In A10 enter: ENTER PERCENT: Name B10 "Percent" and format this cell by means of the %-icon. In A11 enter: TOTAL EQUALS: In B11 enter: =VLOOKUP(Percent,TOTALS,LOOKUP(Amount,AMOUNTS,1)+1,1) Note that if you didn't or couldn't name the ranges and cells as I indicated, then use the actual references in the formula of B11. Note also that LOOKUP in the formula determines the column of AMOUNTS where to look for a total given a percent. Aladin

Bis:
Replace LOOKUP with MATCH.
I plugged in the wrong function in that VLOOKUP-formula.

Aladin

Posted by Bis on April 10, 2001 5:43 AM

Re: Correction...

Thanks for everything Aladin. It worked GREAT!

Best Regards,
Bis