It would be helpful if we had some sample data for row 16, but I'll do what i
can
There are contents in row 16,
columns D, H, J and L
the overall action is that there is a calculation performed and compared to the
number in cell D16. if it is less than or = D16, then a "1" is placed in the
cell that has this formula
if it is greater than the value in D16, then the cell is left blank.
Now the calculations
first, the functions row() and column() return the value of the row number and
column number
for the cell that contains this formula.
So, for the formula in cell E6, the row is 6, the column is 5.
as an example, let's assume the formula is in E6, and J16 = 10, L16 = 15
:
the formula is broken down as:
($J$16-ROW())*10 ############ part1
+
11*(IF($H$16="R",0,1)) ############ part2
+
======================
(-1)^(IF($H$16="R",0,1)) ############ part3
*
(($L$16-COLUMN())+1)
======================
-------------------------------------------
############ part1
-------------------------------------------
($J$16-ROW())*10
it takes the value in J16, subtracts 6 (from row()) and multiplies the result
by 10
resulting in (10-6)*10 = 40
-------------------------------------------
############ part2
-------------------------------------------
11*(IF($H$16="R",0,1))
if H16 is "R", then it multiplies 11 by (0), resulting in "0" of course.
if H16 is NOT "R", , then it multiplies 11 by (1), thus returning 11.
-------------------------------------------
############ part3
-------------------------------------------
(-1)^(IF($H$16="R",0,1))
if H16 is "R", then (-1) is raised to the power of 0, which results in (1)
if H16 is NOT "R", then (-1) is raised to the power of 1, which is (-1)
----------
(($L$16-COLUMN())+1)
takes the value of L16 (15), subtracts the column number (5 for "E") and adds
(1)
resulting in this case with 15-5+1 = 11
-------------------------------------
this result is multiplied with the result from "part 3", which
if H16 is "R", then "part 3" is multiplied by (1), or
if H16 is NOT "R", then "part 3" is multipied by (-1).
so, if H16 is "R", then:
40 + 0 + (1*11) = 51
if H16 is NOT "R", then
40 + 11 + (-1*11) = 40
Now, then the calculation is compared to the value in D16.
Let's say that the value in d16 is 45.
then, if J16 = 10, L16 = 15
and H16 = "R", then 51 is NOT <= 45, so the E6 will be blank ("") and the
conditional formatting turns the cell grey.
if H16 does NOT = "R", then a 40 IS <= 45, a "1" appears in E6, and the
conditional formatting turns the cell magenta.
hope this helps break down the formula.
however, I STILL have no idea WHY someone would want to do this!!!
Paul
________________________________
From: sachin or <sachin...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Wed, May 19, 2010 9:17:58 AM
Subject: $$Excel-Macros$$ What does this formula do?
Hi All,
Please help me out on this formula.
I would require an expanation for each action taken by formula.
=IF(($J$16-ROW())*10+11*(IF($H$16="R",0,1)) +
(-1)^(IF($H$16="R",0,1))*(($L$16-COLUMN())+1)<=$D$16,1,"")
Attached is the file for reference.
--
Regards,
SACHIN OR
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
