Hi Paul,
Thanks so much for your reply and i really appreciate you help on the
fomula break up.
I have few questions below:
Why is this (-1)^ and ())*10+11* mentioned and what is it essential for?
Actions?
Is there a similar fomula which does the same?
It will be a great help if you help on this.
Regards
SACHIN OR
On Wed, May 19, 2010 at 10:56 PM, Paul Schreiner <schreiner_p...@att.net>wrote:
> 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
>
--
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
