Greetings,
I'm trying to create a MIN-MAX formula that will retrieve the MIN and MAX values in a range. The trouble I'm having is, the range contains two types of values. Some contain a $, others contain a %. Essentially, I would like to return the MIN and MAX for both types of values so that if my range looked something like
1$
2$
5%
7%
8$
10%
The formula would return something like: 1$-8$, 5%-10%
Any assistance would be greatly appreciated!
Hi and welcome to the forum
Why on earth would you have a mix of info like that??
I would suggest that you create 2 helper columns and extract each type of data seperately - then you can min and max to your heart's content
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Are these entries actual number values or are they TEXT entries?
If you select a cell that displays as 1$, what do you see in the formula bar?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
The range has been extracted from a series of rows, so the formula bar simply has the INDEX formula I was using (ribbon shows General formatting) - the format doesn't matter to me as long as it displays "1%" or "1$"
The problem with creating two helper columns is that I need to keep the ordering in order return the MIN-MAX values for specific cells. Basically I have something like
Doctors Office 35$
Specialist Office 40$
Chiro visit 40%
Allergy Shots 15%
I have a template that needs to draw the MIN-MAX for those cells and it needs to return as "35$-40$, 15%-40%"
Furthermore, different cells in my template will be looking at different costs - such as A1 needs to look at Doctors Office, Specialist Office, and Allergy Shots. This should return "35$-40$, 15%-15%. A2 needs to look at just Specialist Office, and would return 40$, and so on, with different combinations.
Thank you all for your quick response!
OK, so is there some rule that can be applied that would identify certain "categories" as being % and others as being %....and would the % ever be greater than 1 (100%) and the $ ever be less than 1. eg 120% and $0.50
The % will not be greater than 100%, and the $ will not be less than 0.
Thanks
ok, and the 1st part of my question?
ALso, if there is a solution in VBA, that would be even better.
Many thanks!
There is nothing determining whether the specific categories will be a % or a $ - if by rule you mean something like "Doctors Office" will always be $. This will be subject to change, as I pull in different tables, the "Doctors Office" category may be $, or %.
Apologies if I misunderstood your question.
Last edited by JoKnows; 03-23-2013 at 08:32 PM.
adding he;per columns shouldnt alter the the order (sequence?) of your data?
Im thinking of something likeThe problem with creating two helper columns is that I need to keep the ordering in order return the MIN-MAX values for specific cells.
in B1 =IF(A1>1,A1,"")
in C1 =IF(A1<1,A1,"")
then you could use...
="$"&MIN(B1:B6)&"-$"&MAX(B1:B6)
and
=TEXT(MIN(C1:C6)*100,"0.0")&"%-"&TEXT(MAX(C1:C6)*100,"0.0")&"%"
I believe this will work. Thanks so much, I really appreciate it!
One followup question.
Min and Max functions will return a "0" if the range is blank. Is there any way to NOT return the 0 if the range is blank? Currently, if I have something like this
Doctors Office 40%
The formula will return: $0-$0,40%-40%
I need it to return 40%-40%
Thanks
OK that wasnt what you asked for in post 12...
=if(sum(the-range-you-used-for-%)=0,"0%-0%",your-min/max-formula)&if(sum(the-range-you-used-for-$)=0,"$0-$0",","&your-min/max-formula)Originally Posted by JoKnows
without seeing your formula, try something like this....
=if(sum(the-range-you-used-for-%)=0,"",your-min/max-formula)&if(sum(the-range-you-used-for-$)=0,"",","&your-min/max-formula)
That would work, but if the range contained values of 0$ or 0%, they would not be calculated in the Min-Max result. I need 0$ and 0% to be calculated, but blanks ignored.
if the sum of the range is 0, then you wont have a min or a max, thats why I used that approach? If the range contained other values besides 0, then the sum of that range would not be 0, and the min/max would then be calculated
I understand. The issue I'm having is that I need the result to be the Min-Max for $ AND the Min-Max for %. In some cases I may have 0$ for all values in the $ column, and values greater than 0 in the % column. So, the result should be 0$-0$, x%-y%. If I read your formula correctly, the result would be simply x%-y%, excluding the 0$-0$, which wouldn't work for me. If there is a 0$ or 0% in either column (even if it is the only value in that column), I need it to be calculated in the Min-Max result. That being said, if the entire range for either $ or % is blank, I need the result to be blank.
Apologies if I was unclear. Again, I'm very thankful for your help!
I'll try to explain once again.
The formula works fine for Case 1.
Case 1
A B
0$ 50%
0$ 60%
Result I need: 0$-0$,50%-60%
The issue is Case 2.
Case 2
A B
blank 50%
blank 60%
Result I need: 50%-60%
As I said in post 12, if the column is blank, Min and Max read the blank as a "0" and delivers a "0$-0$,50%-60%, which is inaccurate.
I hope that clarifies the issue.
Last edited by JoKnows; 03-24-2013 at 01:43 AM.
Can you post a SMALL sample file (~20 rows worth of data) so we can see what you're trying to do?
Make sure you include the results you expect.
Tony,
See the small file I attached that represents the problem in post 18. As I mentioned, Case 1 is fine. Case 2 needs to show only 50%-50%.
Thanks for your help.
Workbook2.xlsx
May be:
=IF(COUNTIF(B3:B6,"")=0,"$"&MIN(B3:B6)&"-$"&MAX(B3:B6)&",","")&TEXT(MIN(C3:C6)*100,"0.0")&"%-"&TEXT(MAX(C3:C6)*100,"0.0")&"%"
Quang PT
That should work, thanks a bunch!
Are you sure that works?
In your sample file the data in B3:B6 is TEXT. Try changing one of the cell entries to 15 (or any number other than 0) and see what result you get.
Do you absolutely have to have those dollar signs on the right side of the cell entry? That's what is making the cell data type TEXT.
You're right Tony. Thanks for pointing that out. Any suggestions?
Will there ever be a case 3?
Case 1 = 0$ ... 50%
Case 2 = [blank] ... 50%
Case 3 = 0$ ... [blank]
If there can be a case 3 then what result would you expect?
Tony,
Yes, that is a possibility, as is Case 4 = 0$... 0%
Thanks
Ok, let's see if I understand this...
The $ values and the % values will always be in separate columns?
There may or may not be $ values and/or % values in either column?
There can be 4 possible results:
min$ - max$ , min% - max%
min$ - max$
min% - max%
[blank] when there are neither $ values nor % values
Does that cover everything?
Yes, thats correct.
OK, this seems to work...
B3:B6 = Copay
C3:C6 = Coinsurance
This array formula** entered in E3:
=TRIM(IF(COUNTA(B3:B6),MIN(IF(B3:B6<>"",--SUBSTITUTE(B3:B6,"$","")))&"$-"&MAX(IF(B3:B6<>"",--SUBSTITUTE(B3:B6,"$","")))&"$","")&IF(COUNTA(C3:C6)," "&TEXT(MIN(C3:C6),"0%")&"-"&TEXT(MAX(C3:C6),"0%"),""))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
That's perfect. Thanks!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
JoKnows 
Register To Reply
Bookmarks