these are mathematical operators.
do you understand mathematical operators and their precedence?
meaning: exponents process before multiplication and division,
which process before addition/subtraction.
parenthesis are used to force operations to perform in a specific order.
I have no idea what your formula is for, or what it is
supposed to calculate, so I cannot tell if the formula is correct.
the "^" is an exponent operator.
like: 2 to the 2nd power is 2(squared) = 4
3^2 = 9
4^2 = 16
i think the excel function exp() is the equivalent, but why would you use it
here?
it's like saying: instead of 2 + 2, can I use: =plus(2,2)
as for *10+11*
the *10 is multiplying the previous expression ($J$16-ROW()) by 10
and adding the result to the value of 11 times the value of the expression
following...
IF(($J$16-ROW())*10+11*(IF($H$16="R",0,1))
it seems like you don't understand mathematical expressions.
It may be that you need to look into that.
Paul
________________________________
From: sachin or <sachin...@gmail.com>
To: excel-macros@googlegroups.com; schreiner_p...@att.net
Sent: Fri, May 21, 2010 7:33:58 AM
Subject: Re: $$Excel-Macros$$ What does this formula do?
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
--
----------------------------------------------------------------------------------
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
