Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default Range Penetration - formula based on percentage

I have a range of $20 to $50. If I want to pay 70% of the range penetration,
what would the formula be?

Thanks,
D
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Range Penetration - formula based on percentage

Hi D,

To calculate the range penetration based on a percentage, you can use the following formula:
  1. = (Max Value - Min Value) * Percentage + Min Value

In your case, the minimum value is $20 and the maximum value is $50. If you want to pay 70% of the range penetration, you can plug in these values into the formula like this:

Formula:
= ($50 - $20) * 0.7 + $20 
This will give you the result of $38, which is 70% of the range penetration between $20 and $50.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default Range Penetration - formula based on percentage

=(YourFormulaForRangePenetration)*.7

Vaya con Dios,
Chuck, CABGx3



"Danielle" wrote:

I have a range of $20 to $50. If I want to pay 70% of the range penetration,
what would the formula be?

Thanks,
D

  #4   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Range Penetration - formula based on percentage

Another interp ..

If in A1: $20, in B1: $50,
then perhaps in C1:
=(B1-A1)*0.7+A1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Danielle" wrote:
I have a range of $20 to $50. If I want to pay 70% of the range penetration,
what would the formula be?

Thanks,
D

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default Range Penetration - formula based on percentage

No... i needed to penetrate 70% of the range. I don't know what that
formula is.


For example:
I have $30.
My range is $10.00 - $60. The formula to find percentage is
A B C
D
1 Have Min Range Max Range
Range penetration
2 $30.00 $10.00 $60.00
=sum(a2-b2)/(c2-b2)

answer is 40%
Which means, $30 penetrates the range by 40%. If the "have" was $60. then
it would penetrate the range by 100%, and if I "have" $10. then I penetrate
the range by 0%

The above scenario, I have the formula to get the answer for column D.
The next scenario, I need the formula to get the answer for colunm A

A B C
D
1 Have Min Range Max Range
Range penetration
2. ???? $10.00 $60.00
70%




"CLR" wrote:

=(YourFormulaForRangePenetration)*.7

Vaya con Dios,
Chuck, CABGx3



"Danielle" wrote:

I have a range of $20 to $50. If I want to pay 70% of the range penetration,
what would the formula be?

Thanks,
D



  #6   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Range Penetration - formula based on percentage

Danielle, some thoughts ..

=sum(a2-b2)/(c2-b2)


above can be changed to just:
=(A2-B2)/(C2-B2)

(think the SUM is extraneous)

Then to derive the calc "backwards", using a bit of algebra:

Put in say, A3:
=D3*(C3-B3)+B3

For the same values in C3:D3 of: 10, 60, 0.4
A3 will return: 30

Another way to directly run the calc "backwards" based is to use Goal Seek

Try playing with this feature in this manner:

Select D2, click Tools Goal Seek,
then make the settings in the Goal Seek dialog:

Set cell: D2
To value: 0.7
By changing cell: A2

Click OK, and the Goal Seek Status dialog
will show whether a solution has been found.
Click OK to accept the solution.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Danielle" wrote:
No... i needed to penetrate 70% of the range. I don't know what that
formula is.


For example:
I have $30.
My range is $10.00 - $60. The formula to find percentage is
A B C
D
1 Have Min Range Max Range
Range penetration
2 $30.00 $10.00 $60.00
=sum(a2-b2)/(c2-b2)

answer is 40%
Which means, $30 penetrates the range by 40%. If the "have" was $60. then
it would penetrate the range by 100%, and if I "have" $10. then I penetrate
the range by 0%

The above scenario, I have the formula to get the answer for column D.
The next scenario, I need the formula to get the answer for colunm A

A B C
D
1 Have Min Range Max Range
Range penetration
2. ???? $10.00 $60.00
70%

  #7   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Range Penetration - formula based on percentage

Incomplete line:
Another way to directly run the calc "backwards" based is to use Goal Seek


should have read as:

Another way to directly run the calc "backwards" based on what is framed up
in your row2 [ie with D2 housing the formula: =(A2-B2)/(C2-B2)] is to use
Goal Seek ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
  #8   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 594
Default Range Penetration - formula based on percentage

My take would be if your "range" is $20-50, then that spans $30, so 70% of
$30 is $21, and if it's supposed to be on top of the base of $20, then it
would be $41...........huh?

So, if A1 is $20 and A2 is $50, then
=(A2-A1)*.7 would give you the $21, and
=(A2-A1)*.7+A1 would give you the $42

hth
Vaya con Dios,
Chuck, CABGx3


"Danielle" wrote in message
...
I have a range of $20 to $50. If I want to pay 70% of the range

penetration,
what would the formula be?

Thanks,
D



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to select a specific range based on the time value of user form input Jitranijam New Users to Excel 8 November 15th 06 12:52 AM
color a range of cells based on a criteria Dave F Excel Discussion (Misc queries) 2 October 13th 06 02:29 PM
Lookup Data in two seperate Spreadsheets Padraig Excel Worksheet Functions 6 June 28th 06 03:05 PM
Dynamic Range with unused formula messing up x axis on dynamic graph [email protected] Charts and Charting in Excel 2 February 2nd 06 08:02 PM
Return Range of Numerical Values in Single Column based on Frequency Percentage Sam via OfficeKB.com Excel Worksheet Functions 9 October 28th 05 11:01 PM


All times are GMT +1. The time now is 08:46 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"