Take the 2-minute tour ×

I am trying to create a statistical table based on columns of information. The row/column identifiers of the table are also incorporated in part of the statistics of the table. I have three columns of information including a min, max, and middle statistics. Keep in mind that while i am showing only one example of the numbers there are actually hundreds of occurrences

    MIN     MAX     MIDDLE 
    1.36790 1.34740 1.34920

And the following is the table that i would like to set up with the columns being the MIN and the rows being the MAX.

                0.00100   0.00500   0.01000

This is a multi part question and for the terms of simplicity I will only ask one for know. My question is this. Starting with the first column and MIN of 0.00100. I want to find a formula where if the Middle minus the MIN is large than the respective column in the table (in this instance 0.00100) than the MIN is automatically returned otherwise the middle is returned. In full this question will go down the entire length of the original columns. What i want to do is add the sum of all of the mins that are returned along with the middles that are returned. In only that first empty cell of the table. I plan on repeating this process for all of the other cells of the table as well. If you have any questions regarding this process please ask and I will try to further explain my issue to help you better understand it.

share|improve this question

migrated from stackoverflow.com Dec 16 '12 at 21:38

This question came from our site for professional and enthusiast programmers.

The structure of this looks like a pivot table. You can select min as the row labels, and it will list all unique values as row labels. You can select max as the column labels and it will list all unique values as column labels. That will get you your table structure. Then I suppose you'd write some VBA to walk across the rows of the table, implementing the formula you stated. –  kermit Dec 14 '12 at 4:24
add comment

1 Answer

If I am understanding correctly what you want to achieve and how your data are structured, array formulas using SUM and IF can calculate what you need.

I'll assume the following setup:

  • Range A2:A10 holds the MIN values.
  • Range C2:C10 the MID values.
  • Cell B12 holds the 0.001 value.
  • Cell B13 will hold the first sum (for 0.001)

The following array formula does the calculation.

             ---MID---- ----MIN---- 0.001 ---MIN--- ---MID----

    (use the CTRL-SHIFT-ENTER key combination rather than the ENTER key 
     to enter this formula)

Copying it to the right will do the sum for 0.005, 0.01, etc.

If you are not familiar with Excel array formulas, there is a really great explanation of how they work and what you can do with them here.

share|improve this answer
add comment

Your Answer

