• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

percentage with multiple variables

M

Maggie

New Member
Hi,

I'm trying to calculate the percentage of data points that meet multiple variables.

B1:B2 min and max numbers
First:Last!$B$245:$K$274 data points


=(INDEX(FREQUENCY(First:Last!$B$245:$K$274,$B$1:$B$2),2)/COUNT(First:Last!$B$245:$K$274))

How to I modify the formula to include the variable in AK2:AK3 dates

Thanks
 
I think it would be easier to setup this type of formula on each sheet in your First:Last sheets.
=COUNTIFS(Range1,Criteria1,Range2,Critera2)
Assuming that formula is in cell Z1, overall formula then is
=SUM(First:Last!Z1)/COUNT(First:Last!B245:K274)
 
Hi,

I don't think that will work. I didn't explain myself well.:oops:

I have a stats sheet with the formulas.

On the stats sheet
min/max numbers (B1:B2, C2:C3, D2:D3, E2:E3, F2:F3)
dates (AK2:AK3, AK4:AK5, etc)

On the data sheets
dates A:A
data points B:K

B245 -50
C246 -150
J270 - 150
K274 - 50


Formula should return 50% (2 of the 4 numbers between 1-99) B1:B2
Should return 50% (2 of the 4 numbers between 100-199) C1:C2

I have it now First:Last!B245:K274 is September, October First:Last!B275:K305, etc
Trying to modify it so if I want to know the % for Sept 15 - Oct 15 all I have to do is change the dates instead of changing it to First:Last!B259:K289

Thanks for your help
 
Hi Maggie ,

The data and the layout you have described is fairly complicated ; is it not possible for you to upload your workbook ? It would have been simpler for you , and you could have got a solution by now.

Personally speaking , I find it too much trouble to go through your description , visualize everything and then try and come up with a formula ; if your workbook were available , my work would reduce.

Narayan
 
Hi Maggie ,

I am not able to make head or tail of your requirement , sorry.

I find nothing in column AK ; I find your formulae in rows 5 through 11 are all mixed up ; the formulae in column G are mixed up and inconsistent.

Sorry , but I am sure others will pitch in to help.

Narayan
 
Hi Narayan,

I uploaded the wrong file. Please take another look at it.
 

Attachments

  • chandoo.xlsx
    670.3 KB · Views: 11
Hi Maggie ,

It's late tonight ; I can check it out only tomorrow morning. In case no one else pitches in till then , I'll get back to you tomorrow.

Narayan
 
Hi Maggie ,

Can you clear the following doubt ?

What are the dates in the range R2 through W3 ? Is it that the date range R2:R3 is to be applied only to the sheet tab Seventh , and the date range S2:S3 to be applied only to the sheet tab Eighth ?

In such a case , the total count in G4 would be just the sum of G5 and G6.

What role does the date in A3 play ? Are the dates in the range R2 through W3 to be derived based on this date ?

In the individual sheet tabs Seventh and Eighth , what is the role of the columns M through P ?

Narayan
 
Hi Maggie,
Trying to reference multiple 2-D ranges on separate sheets in a single calculation without referring to each range individually will require some sophisticated techniques... I do not know any.

However, I am not sure you need such sophisticated techniques...

Without knowing more about your data, I am not sure whether the following suggestions would help you or not...

If all of your sheets (Seventh, Eighth, etc.) have the same layout, same number of rows, cover the same date range (col A), and have the same number of columns for data points (B:K), and the data points for a given date do not overlap,
then perhaps you can sum all of the individual 2-D ranges, and then perform further operations
For example (Seventh!B3:K1000 + Eighth!B3:K1000 + ...) You could even setup a Named range for this, so that you can then refer to that Name in further calculations (like MIN, MAX, etc.)

If the data points for a given date overlap on various sheets, then the approach that you have already started might be the way to go...
Create a column on each sheet that SUMs each row, calculates the MIN for each row, MAX for each row, etc.
For the COUNT, I would suggest setting up individual columns for the various ranges (1-99,100-199, etc.) on each sheet, instead of referring back to another sheet. You can always setup the ranges elsewhere, and then reference them in the column header on the individual sheets, allowing you to modify the ranges easily.

Based on the formulas you are using on the sample worksheet, my impression is that you know your way around Excel, and would be able to adopt the above suggestions easily. If not, let us know, and we can modify your sample worksheet.

Cheers,
Sajan.
 
NARAYANK991 said:
Hi Maggie ,

Can you clear the following doubt ?

What are the dates in the range R2 through W3 ? Is it that the date range R2:R3 is to be applied only to the sheet tab Seventh , and the date range S2:S3 to be applied only to the sheet tab Eighth ?

In such a case , the total count in G4 would be just the sum of G5 and G6.

What role does the date in A3 play ? Are the dates in the range R2 through W3 to be derived based on this date ?

In the individual sheet tabs Seventh and Eighth , what is the role of the columns M through P ?

Narayan
Click to expand...


Hi,

R2:R3 1 Jan & 31 Jan
S2:S3 1 Feb & 28 Feb

Dates are applied to all sheets. Each sheet has its own data.

A3 is just a header for the month's stats. In this example Jan 07

Dates in the range R2 through W3 are 'used' in other months' formulas. Feb, Mar, etc

Role of M and P worked with an earlier workbook; had lots of help building it. Used to calculate in tables that caused the workbook to crash repeatedly. Left them in the workbook to show what was tried before, should of mentioned the tables.

Thanks for your help
 
Sajan said:
Hi Maggie,
Trying to reference multiple 2-D ranges on separate sheets in a single calculation without referring to each range individually will require some sophisticated techniques... I do not know any.

However, I am not sure you need such sophisticated techniques...

Without knowing more about your data, I am not sure whether the following suggestions would help you or not...

If all of your sheets (Seventh, Eighth, etc.) have the same layout, same number of rows, cover the same date range (col A), and have the same number of columns for data points (B:K), and the data points for a given date do not overlap,
then perhaps you can sum all of the individual 2-D ranges, and then perform further operations
For example (Seventh!B3:K1000 + Eighth!B3:K1000 + ...) You could even setup a Named range for this, so that you can then refer to that Name in further calculations (like MIN, MAX, etc.)

If the data points for a given date overlap on various sheets, then the approach that you have already started might be the way to go...
Create a column on each sheet that SUMs each row, calculates the MIN for each row, MAX for each row, etc.
For the COUNT, I would suggest setting up individual columns for the various ranges (1-99,100-199, etc.) on each sheet, instead of referring back to another sheet. You can always setup the ranges elsewhere, and then reference them in the column header on the individual sheets, allowing you to modify the ranges easily.

Based on the formulas you are using on the sample worksheet, my impression is that you know your way around Excel, and would be able to adopt the above suggestions easily. If not, let us know, and we can modify your sample worksheet.

Cheers,
Sajan.
Click to expand...

Hi,

You give me way too much credit, I had lots of help in building the workbook on another forum.

Yes all of my sheets contain the same layout.
A:A dates
B:K data points

I couldn't solve it using two variables; min/max and dates. So I went back to previous workbook changing the individual formulas to match the dates. New workbook crashed excel every time because of the tables that were added to it for the second variable (dates)

The formula below works without dates factored into the formula.

=(INDEX(FREQUENCY(First!$B$2649:$B$2678,$B$1:$B$2),2)/COUNT(First!$B$2649:$B$2678))


I'm trying not to have to change the row variable ($B$2649:$B$2678) every time I add another month to the database. Or, I wonder what does the data tells me for the period of 15 Jul to 13 Dec.

The simpler, the better.

Thanks again for your help.
 
Hi Maggie,
Sounds like you would be able to flatten B:K columns on each worksheet to generate the summaries you need on your summary tab. That would also eliminate the need to handle multiple 2-D ranges in a single formula.

If you do not get any other feedback, I can take a look in another day or two to modify your sample workbook to demonstrate what I was suggesting above. (I will limit the data to one or at most two months to make it easy to see the changes.)

Cheers,
Sajan.
 
You must log in or register to reply here.
Back
Top