Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula explaination
A person typed some numbers in column A (line 2 to line 10)
then in line 12 she wanted to show the maximum number in the column to show that value, the only formula I know is .. =MAX(B6:B30) However, she used the following formula: =R:R+MAX(B6:B30) can any one tell me what the "R:R" stands for? and why there is a plus mark? Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula explaination
As written, the R:R+ does nothing. The R:R refers to the entire column R and
the plus is just a plus (addition symbol). It has the ultimate effect of adding 0 to your Maximum number. Where you'd use R:R would be inside a function that calls for a range. Such as SUM(R:R) would give you the sum of all numbers in column R. HTH, Elkar "r" wrote: A person typed some numbers in column A (line 2 to line 10) then in line 12 she wanted to show the maximum number in the column to show that value, the only formula I know is .. =MAX(B6:B30) However, she used the following formula: =R:R+MAX(B6:B30) can any one tell me what the "R:R" stands for? and why there is a plus mark? Thank you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula explaination
I assume you confused A2:A10 and B6:B30?
You can see what happens if you click the Trace Precedents button. If there is nothing in the same row in column R, R:R results in zero and you get the result you require. But it is strange she did it that way. R also means Row in the R1C1 reference style. The way it is used here is an example of implicit intersection. Both addressing techniques are used very rarely. I wonder why your colleague did it this way. I don't know of course, but I wouldn't be surprised if she just had no clue at all. But then, your information wasn't exactly precise either....... Please let us know if you find out. -- Kind regards, Niek Otten "r" wrote in message ... |A person typed some numbers in column A (line 2 to line 10) | then in line 12 she wanted to show the maximum number in the column | | to show that value, the only formula I know is .. =MAX(B6:B30) | | However, she used the following formula: | =R:R+MAX(B6:B30) | | can any one tell me what the "R:R" stands for? and why there is a plus mark? | | Thank you |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula explaination
As written, the R:R+ does nothing.
Actually, it does the equivalent of: R1+MAX(B6:B30) Of course, if R1 was empty then it would be: 0+MAX(B6:B30) If the formula: =R:R+MAX(B6:B30) was entered as an array into a range of cells then it would be the equivalent of: R1+MAX(B6:B30) R2+MAX(B6:B30) R3+MAX(B6:B30) R4+MAX(B6:B30) etc etc Biff "Elkar" wrote in message ... As written, the R:R+ does nothing. The R:R refers to the entire column R and the plus is just a plus (addition symbol). It has the ultimate effect of adding 0 to your Maximum number. Where you'd use R:R would be inside a function that calls for a range. Such as SUM(R:R) would give you the sum of all numbers in column R. HTH, Elkar "r" wrote: A person typed some numbers in column A (line 2 to line 10) then in line 12 she wanted to show the maximum number in the column to show that value, the only formula I know is .. =MAX(B6:B30) However, she used the following formula: =R:R+MAX(B6:B30) can any one tell me what the "R:R" stands for? and why there is a plus mark? Thank you |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula explaination
Hmm... my test resulted in no change regardless of what data was entered in
cell R1. Your response prompted further testing. You are correct only if the formula is also entered in row 1. So, to amend my original response, the R:R+ does do something after all. It will take the value found in Column R of the same row that the formula is entered into. Then add that value to the Maximum number. So, if the formula is entered into cell B31, then the result would be the value of cell R31 + the maximum value found in (B6:B30). HTH, Elkar "Biff" wrote: As written, the R:R+ does nothing. Actually, it does the equivalent of: R1+MAX(B6:B30) Of course, if R1 was empty then it would be: 0+MAX(B6:B30) If the formula: =R:R+MAX(B6:B30) was entered as an array into a range of cells then it would be the equivalent of: R1+MAX(B6:B30) R2+MAX(B6:B30) R3+MAX(B6:B30) R4+MAX(B6:B30) etc etc Biff "Elkar" wrote in message ... As written, the R:R+ does nothing. The R:R refers to the entire column R and the plus is just a plus (addition symbol). It has the ultimate effect of adding 0 to your Maximum number. Where you'd use R:R would be inside a function that calls for a range. Such as SUM(R:R) would give you the sum of all numbers in column R. HTH, Elkar "r" wrote: A person typed some numbers in column A (line 2 to line 10) then in line 12 she wanted to show the maximum number in the column to show that value, the only formula I know is .. =MAX(B6:B30) However, she used the following formula: =R:R+MAX(B6:B30) can any one tell me what the "R:R" stands for? and why there is a plus mark? Thank you |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula explaination
You are correct only if the formula is also entered in row 1
Try this: Enter some random numbers in column R. Enter some random numbers in B3:B30 Select the range A10:A15 Enter this formula as an array: R:R+MAX(B3:B30) A10 = R1+MAX(B3:B30) A11 = R2+MAX(B3:B30) A12 = R3+MAX(B3:B30) etc Biff "Elkar" wrote in message ... Hmm... my test resulted in no change regardless of what data was entered in cell R1. Your response prompted further testing. You are correct only if the formula is also entered in row 1. So, to amend my original response, the R:R+ does do something after all. It will take the value found in Column R of the same row that the formula is entered into. Then add that value to the Maximum number. So, if the formula is entered into cell B31, then the result would be the value of cell R31 + the maximum value found in (B6:B30). HTH, Elkar "Biff" wrote: As written, the R:R+ does nothing. Actually, it does the equivalent of: R1+MAX(B6:B30) Of course, if R1 was empty then it would be: 0+MAX(B6:B30) If the formula: =R:R+MAX(B6:B30) was entered as an array into a range of cells then it would be the equivalent of: R1+MAX(B6:B30) R2+MAX(B6:B30) R3+MAX(B6:B30) R4+MAX(B6:B30) etc etc Biff "Elkar" wrote in message ... As written, the R:R+ does nothing. The R:R refers to the entire column R and the plus is just a plus (addition symbol). It has the ultimate effect of adding 0 to your Maximum number. Where you'd use R:R would be inside a function that calls for a range. Such as SUM(R:R) would give you the sum of all numbers in column R. HTH, Elkar "r" wrote: A person typed some numbers in column A (line 2 to line 10) then in line 12 she wanted to show the maximum number in the column to show that value, the only formula I know is .. =MAX(B6:B30) However, she used the following formula: =R:R+MAX(B6:B30) can any one tell me what the "R:R" stands for? and why there is a plus mark? Thank you |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula explaination
I agree that when entered as an array formula it will return the Max of
B6:B30 + R1, but when not entered as an array formula, it returns the max of B6:B30 plus whatever is in the same row of column R (as Elkar said). So if the formula is in B31, it will add R31 (or the 31st element from the array). Interesting - I've never seen a formula written like this. It's either genius or madness. "Biff" wrote: You are correct only if the formula is also entered in row 1 Try this: Enter some random numbers in column R. Enter some random numbers in B3:B30 Select the range A10:A15 Enter this formula as an array: R:R+MAX(B3:B30) A10 = R1+MAX(B3:B30) A11 = R2+MAX(B3:B30) A12 = R3+MAX(B3:B30) etc Biff "Elkar" wrote in message ... Hmm... my test resulted in no change regardless of what data was entered in cell R1. Your response prompted further testing. You are correct only if the formula is also entered in row 1. So, to amend my original response, the R:R+ does do something after all. It will take the value found in Column R of the same row that the formula is entered into. Then add that value to the Maximum number. So, if the formula is entered into cell B31, then the result would be the value of cell R31 + the maximum value found in (B6:B30). HTH, Elkar "Biff" wrote: As written, the R:R+ does nothing. Actually, it does the equivalent of: R1+MAX(B6:B30) Of course, if R1 was empty then it would be: 0+MAX(B6:B30) If the formula: =R:R+MAX(B6:B30) was entered as an array into a range of cells then it would be the equivalent of: R1+MAX(B6:B30) R2+MAX(B6:B30) R3+MAX(B6:B30) R4+MAX(B6:B30) etc etc Biff "Elkar" wrote in message ... As written, the R:R+ does nothing. The R:R refers to the entire column R and the plus is just a plus (addition symbol). It has the ultimate effect of adding 0 to your Maximum number. Where you'd use R:R would be inside a function that calls for a range. Such as SUM(R:R) would give you the sum of all numbers in column R. HTH, Elkar "r" wrote: A person typed some numbers in column A (line 2 to line 10) then in line 12 she wanted to show the maximum number in the column to show that value, the only formula I know is .. =MAX(B6:B30) However, she used the following formula: =R:R+MAX(B6:B30) can any one tell me what the "R:R" stands for? and why there is a plus mark? Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Nesting questions | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |