The Art of Excel Charting - 2007 Post

[cainorthu]

Hi all!

I found this great post about an alternate way to visualize data and was wondering if anyone can help me understand the formula that returns a 1 or 0 depending on the source cell.

Why is the formula multiplying by 10, adding 11, and multiplying by 0 or 1?

What does adding (-1)^ do to the formula?

I tried to break it out into its component parts, but this is beyond my current knowledge of how formulas interact.

= IF((ROW($a$10)-ROW())*10+11*(IF($a$12=”R”,0,1)) + (-1)^(IF($a$12=”R”,0,1))*((COLUMN($j$10)-COLUMN())+1)< =$a$11,1,"")

This is the link to the post: http://chandoo.org/wp/2007/06/27/the-art-of-excel-charting/

Please let me know if this has already been answered and I will remove this post (I couldn't find a post related to this...)

Thanks so much in advance!

 

[NARAYANK991]

Hi ,

The best way to understand this formula is by actually entering it in a worksheet , and varying the two parameters.

1. Select about 200 columns , and change their column widths to 2.

2. A11 and A12 are the two cells which will contain the parameters.

3. Starting with column B , enter the formula in the range B1:B50.

4. Copy this range to the next 200 columns.

5. Vary A12 with values of R or L ( R is for right and L is for left )

6. Vary A11 with values from -100 to +100

See how the pattern varies with these two parameters.

A detailed explanation will follow.

Narayan

 

[cainorthu]

Narayan,

Thank you so much for your reply! I am currently implementing that technique but I am still running into a few things I don't understand. Given time, I'm sure I can figure them out but any help would be greatly appreciated.

Thanks

 

[NARAYANK991]

Hi ,

I have rewritten the formula with named ranges , so that it is easier to visualize what is happening. The formula is :

=IF((StartRow-ROW())*StartRow+(StartRow+1)*(IF(RightOrLeft="R",0,1)) + (-1)^(IF(RightOrLeft="R",0,1))*((StartColumn-COLUMN())+1)<=A11Value,1,"")

Thus , there are 3 components to the formula : the first which is colored BLUE , the second which is colored MAGENTA , and the third which is highlighted BOLD.

The first component contains only the row value , while the second contains only the column value. Together , they generate a value , which is then compared with a preset value which is in the named range A11Value ; based on this comparison , the cell in which this formula is entered will display either 1 or a blank.

It is clear that as the row number exceeds the value in the named range StartRow , the sum of these two values will go negative. Similarly , as the column number exceeds the value in the named range StartColumn , the sum of these two values will go negative.

What is the role of this portion of the formula :

(-1)^(IF(RightOrLeft="R",0,1))

If the named range RightOrLeft is R , then the output of the IF function is 0 , and the result of the above expression will be 1 ( any number raised to the power 0 is 1 ).

If the named range RightOrLeft is not R , then the output of the IF function is 1 , and the result of the above expression will be -1 ( -1 raised to the power 1 is -1 ).

We can summarize by saying that if RightOrLeft is R , then the above expression will be 1 , otherwise it will be -1.

Thus , based on RightOrLeft , the second component involving the column number will either add to the first component involving the row number , or subtract from it.

You can visualize a series of steps either starting from a low level and rising to the right , or starting at a high level and falling to the right.

What is the role of the named range A11Value ?

It is clear that when the left hand side ( combination of the row component and column component ) is less than A11Value , the result of the IF function , and the overall formula , will be 1.

So if a positive value is chosen for A11Value , there will be more 1s than if a negative value is chosen , because as the row numbers and column numbers increase , the combination of the two components will only go negative and the left hand side combination will be less than a positive value of A11Value.

See the attached file , and see what happens as you change the 4 parameters.

Narayan