Evaluation same, result different?

I selected A22:A27 then input the following:
{=CHAR(97+ROW(A22:A27)-ROW(A22))}    CSE
Though I get a,b,c ... e,f in cells A22:A27, but if I evaluate going into 
each of the cells in this range, the evaluator returns CHAR(97) always, but 
then how come the resultant character changes? Any ideas? 


0
padhye.m (42)
5/17/2007 6:23:11 PM
excel 39879 articles. 2 followers. Follow

6 Replies
505 Views

Similar Articles

[PageSpeed] 8

Thu, 17 May 2007 23:53:11 +0530 from dindigul <padhye.m@gmail.com>:
> I selected A22:A27 then input the following:
> {=CHAR(97+ROW(A22:A27)-ROW(A22))}    CSE
> Though I get a,b,c ... e,f in cells A22:A27, but if I evaluate going into 
> each of the cells in this range, the evaluator returns CHAR(97) always, but 
> then how come the resultant character changes? Any ideas? 

The value of row(A22:A27) is 22 if it's not inside an array formula.

-- 
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
                                  http://OakRoadSystems.com/
0
5/17/2007 8:05:47 PM
You need to select a single cell in the array, then just click the evaluate 
button until it changes to restart in the evaluation box and it will end 
with the letter

-- 
Regards,

Peo Sjoblom


"dindigul" <padhye.m@gmail.com> wrote in message 
news:%23ZtMvBLmHHA.4852@TK2MSFTNGP03.phx.gbl...
>I selected A22:A27 then input the following:
> {=CHAR(97+ROW(A22:A27)-ROW(A22))}    CSE
> Though I get a,b,c ... e,f in cells A22:A27, but if I evaluate going into 
> each of the cells in this range, the evaluator returns CHAR(97) always, 
> but then how come the resultant character changes? Any ideas?
> 


0
terre081 (3244)
5/17/2007 8:10:18 PM
Would you be more comfortable with something like this *non*-array formula 
instead:

=CHAR(97+ROWS($1:1)-1)

Entered *anywhere*, will return the alphabet as it's copied down.
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"dindigul" <padhye.m@gmail.com> wrote in message 
news:%23ZtMvBLmHHA.4852@TK2MSFTNGP03.phx.gbl...
>I selected A22:A27 then input the following:
> {=CHAR(97+ROW(A22:A27)-ROW(A22))}    CSE
> Though I get a,b,c ... e,f in cells A22:A27, but if I evaluate going into 
> each of the cells in this range, the evaluator returns CHAR(97) always, 
> but then how come the resultant character changes? Any ideas?
> 


0
ragdyer1 (4060)
5/17/2007 8:18:30 PM
My question was that even when the result is char(97) throughout, how come 
the characters change from a to b to c ...? I do not want any alternate 
solution. I wanted to know about this strange behaviour.
"Ragdyer" <ragdyer@cutoutmsn.com> wrote in message 
news:%2318SRDMmHHA.4032@TK2MSFTNGP02.phx.gbl...
> Would you be more comfortable with something like this *non*-array formula 
> instead:
>
> =CHAR(97+ROWS($1:1)-1)
>
> Entered *anywhere*, will return the alphabet as it's copied down.
> -- 
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "dindigul" <padhye.m@gmail.com> wrote in message 
> news:%23ZtMvBLmHHA.4852@TK2MSFTNGP03.phx.gbl...
>>I selected A22:A27 then input the following:
>> {=CHAR(97+ROW(A22:A27)-ROW(A22))}    CSE
>> Though I get a,b,c ... e,f in cells A22:A27, but if I evaluate going into 
>> each of the cells in this range, the evaluator returns CHAR(97) always, 
>> but then how come the resultant character changes? Any ideas?
>>
>
> 


0
padhye.m (42)
5/17/2007 8:34:14 PM
because the +ROWS($1:1)-1 is added to the 97

paste that part in the rows and see

dindigul wrote:
> My question was that even when the result is char(97) throughout, how come 
> the characters change from a to b to c ...? I do not want any alternate 
> solution. I wanted to know about this strange behaviour.
> "Ragdyer" <ragdyer@cutoutmsn.com> wrote in message 
> news:%2318SRDMmHHA.4032@TK2MSFTNGP02.phx.gbl...
> 
>>Would you be more comfortable with something like this *non*-array formula 
>>instead:
>>
>>=CHAR(97+ROWS($1:1)-1)
>>
>>Entered *anywhere*, will return the alphabet as it's copied down.
>>-- 
>>Regards,
>>
>>RD
>>
>>---------------------------------------------------------------------------
>>Please keep all correspondence within the NewsGroup, so all may benefit !
>>---------------------------------------------------------------------------
>>"dindigul" <padhye.m@gmail.com> wrote in message 
>>news:%23ZtMvBLmHHA.4852@TK2MSFTNGP03.phx.gbl...
>>
>>>I selected A22:A27 then input the following:
>>>{=CHAR(97+ROW(A22:A27)-ROW(A22))}    CSE
>>>Though I get a,b,c ... e,f in cells A22:A27, but if I evaluate going into 
>>>each of the cells in this range, the evaluator returns CHAR(97) always, 
>>>but then how come the resultant character changes? Any ideas?
>>>
>>
>>
> 
> 

0
birelan (531)
5/17/2007 8:47:43 PM
It's really *not* strange behavior for certain array formulas.

It might help you to read up on XL's different array performances.

Arrays are rectangular ranges of formulas OR values, that XL treats as a 
single group.
Some array formulas return an array of results that can appear in *many 
cells*.
Other formulas affect an entire array of cells, yet return the result to a 
*single* cell.

As far as your question is concerned:
Say A1 to A5 contain prices.
B1 to B5 contain quantities.
In C1 you enter the formula:

=A1*B1

Copying this formula down to C5 results in 5 *different* formulas, one in 
each cell.

Now, select D1 to D5, and enter this formula in D1, the cell in focus:

=A1:A5*B1:B5

And use CSE to enter it.

You see the exact same returns in each column (C & D),
BUT ... as you click in each cell in D1 to D5, you see the *same* formula.

XL is using a *single* formula to compute multiple results, in place of 5 
individual formulas.

This is a case where XL is using *less* memory and storage with the array 
formula, then is used by the 5 individual formulas.

There are many cases, of course, where array formulas use *more* of XL's 
resources then regular formulas.
-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"dindigul" <padhye.m@gmail.com> wrote in message 
news:%23Z8i%23KMmHHA.2272@TK2MSFTNGP02.phx.gbl...
> My question was that even when the result is char(97) throughout, how come 
> the characters change from a to b to c ...? I do not want any alternate 
> solution. I wanted to know about this strange behaviour.
> "Ragdyer" <ragdyer@cutoutmsn.com> wrote in message 
> news:%2318SRDMmHHA.4032@TK2MSFTNGP02.phx.gbl...
>> Would you be more comfortable with something like this *non*-array 
>> formula instead:
>>
>> =CHAR(97+ROWS($1:1)-1)
>>
>> Entered *anywhere*, will return the alphabet as it's copied down.
>> -- 
>> Regards,
>>
>> RD
>>
>> ---------------------------------------------------------------------------
>> Please keep all correspondence within the NewsGroup, so all may benefit !
>> ---------------------------------------------------------------------------
>> "dindigul" <padhye.m@gmail.com> wrote in message 
>> news:%23ZtMvBLmHHA.4852@TK2MSFTNGP03.phx.gbl...
>>>I selected A22:A27 then input the following:
>>> {=CHAR(97+ROW(A22:A27)-ROW(A22))}    CSE
>>> Though I get a,b,c ... e,f in cells A22:A27, but if I evaluate going 
>>> into each of the cells in this range, the evaluator returns CHAR(97) 
>>> always, but then how come the resultant character changes? Any ideas?
>>>
>>
>>
>
> 


0
ragdyer1 (4060)
5/18/2007 12:23:28 AM
Reply:

Similar Artilces:

Highlight Cells with different values
Hi, I have a column that I want to locate the differences. I can use the F5---Special---Column Differences. But I wanted to know if I can do it with Conditional Formatting. Like if Value in A1 is different than the values in Column A:A, then it is highlighted... Any help appreciated Fuad Sounds like an Excel question..try one of the Excel newsgroups, this one is for Publisher. "Fuad" <Fuad@discussions.microsoft.com> wrote in message news:0536FB95-362E-412F-BD1C-99BF9B10E8CD@microsoft.com... | Hi, | | I have a column that I want to locate the differences. I can use th...

Different Results from the Same Macro
Hello: At the end of this posting, I have VBA code for a macro that I created. This macro was created in Excel 2007 macro. What's puzzling me is that this macro gives different results everytime that it is used. It is run against the same set of data, so I do not understand why it is producing different data in the spreadsheet. The attached macro code "runs against data" in a spreadsheet in order to generate another spreadsheet. Different results are given every time the macro runs. That's not good. The results need to be consistent. Is there anyt...

Have you evaluated...
Has anyone evaluated Celerant Command Retail or Tomax Retail.net POS solutions? How are they compared to RMS in terms of features and prices? ...

How to Combine Data in Different Columns
Hi Everyone I need help in this issue. I have tried many ways though some wer successful but they were not efficient. I have say 5 columns of data and the data could be on the same rows o all 5 columns or on different rows hence if I need to combine all th data of these 5 columns, I would have to sort or filter each of the manually and copy and paste to a different worksheet or column. This i very tedious and time consuming. I also tried to write a macro to loo for data in one column then put them into another column but i was no successful. However, experts like you guys shouldn't have ...

When "IF" gives result of zero
In Excel 2000 on a UK keyboard, """" will check for, or leave, a cell blank. Having used the "IF" function I want to leave the the result cell displaying a blank . How can I get the same result with Excel 2007? It works the same in 2007. What are you getting? HTH Bob "drus" <drus@discussions.microsoft.com> wrote in message news:1B0C9262-6716-4D04-9379-90528C87F28F@microsoft.com... > In Excel 2000 on a UK keyboard, """" will check for, or leave, a cell > blank. > Having used the "IF" functi...

Downloadable evaluation version of Outlook 2003
does anyone know if it possible to get a downloadable version? Thanks.....Sly Sly wrote: > does anyone know if it possible to get a downloadable > version? > > Thanks.....Sly No. Not full Outlook, only Outlook Express is downloadable along with IE... >-----Original Message----- >does anyone know if it possible to get a downloadable >version? > >Thanks.....Sly >. > ...

sharing offline folders among different profiles
I would like to share (ie use the same folders) my offline Outlook contacts and calendar folders in two different profiles. How? Many thanks. OST files can be opened only in the profile/mailbox for which they were created. What is it you need to accomplish? There may be another way. rbuce@btoauto.com wrote: > I would like to share (ie use the same folders) my offline > Outlook contacts and calendar folders in two different > profiles. How? Many thanks. rbuce@btoauto.com <anonymous@discussions.microsoft.com> wrote: > I would like to share (ie use the same folder...

copy spaced cells to different spacing
On one tab I have formulae in every 4th row that I want to copy onto another tab where I want the same formulae every 2nd row. The rows in between these formulae are not blank. How do I best do this? Derrick You can use code like the following: Sub AAA() Dim Source As Range Dim Dest As Range Dim LastCell As Long Dim WS As Worksheet Set WS = Worksheets("Sheet1") '<<< source of data Set Dest = Worksheets("Sheet2").Range("A1") '<<< destination of copy With WS LastCell = .Cells(.Rows.Count, "A").End(xlUp).Row Set Sour...

Difference in the listing of the New module addition in 2002 version
Hi, I have Excel 2002 at home and also at work. At home when I add a new module (using Insert module) Then it gets listed under "VBAProject (Book1)" and it is in the same tree node as "Sheet1 (Sheet1)", "Sheet2 (Sheet2)" , "Sheet3 (Sheet3)" "This Workbook". There is no separate "Microsoft Excel objects" folder. While at Work when I insert a new Module it gets added under a folder called Module which is basically part of the tree node "VBAProject (Book1)". Also at work I have a separate "Microsoft Excel objects"...

Rules for different smtp addresses for 1 user.
In exchange my user account is set-up with 2 smtp addresses. 1) Erik@mycompany.com and 2) jreed@mycompany.com (this is the primary SMTP address) so any email sent to those 2 addresses comes to my inbox. I tried to create a rule in outlook XP to place the email to erik@mycompany.com into a seperate folder. Turns out that this can not be done! OUCH! it appears all incoming mail is translated to the local account name and the smtp address is not referenced at all???? I do not want to have to create a seperate user and maintain 2 different mailboxes. I have to be missing how to do thi...

Evaluating text as formulae
Is it possible to refer to text in a cell as though it were a formula, ie execute it instead of displaying it? Eg if the result of your formula was "A1 + B2", to actually add A1 and B2 and display the result? Thanks Nigel Like concatenating text? =concatenate(a1," + ",b2) or =concatenate(a1,b2) =concatenate(a1," ",b2) "Nigel Ramsden" wrote: > Is it possible to refer to text in a cell as though it were a formula, ie > execute it instead of displaying it? Eg if the result of your formula was > "A1 + B2", to actually add A1 and...

creating chart from different sheet
Hi Looking for the method to create chart in Excel sourcing cell from different sheets, instead of same sheet.. I already tried hold CTrl key but it gives the format error when i try to add series to the chart if the data is from the two different sheets. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ All the data for the values for one axis for a series on a chart must be on a single worksheet. So, all the x-values for a series must be on a worksheet, just as al...

Troubleshooting same Word versions
In our office, my coworker and I have the same version of WORD and XP, but her view of the exact same document is different (wrong). Mine shows up correctly. We've checked everything we know to check such as VIEW, Tools, Page Setup, settings, etc...and everything matches. Any ideas? Thanks, Peg In what way(s) does it differ? -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Peggy" <Peggy@discussions.microsoft.com> wrote in message news:56038D34-3FEC-4386-9DFD-5FF94B50861E@microsoft.com... &...

appoint show up differently on different computers
Using Exchange 2003, with mixed Outook2000 and Outlook2003 clients. OSes are mixed also, NT4.0, 2000, XP. The problem: In Exchange public folders some computers display the same appointsments differently. For example, we use a public calendar to mark employee birthdays. On some computers the birthdays are showing up as two day events, while other show it as a one day events. There does not be any rhyme or reasons why it appears on one computer a certain way and differently on another Any ideas thank Peter Have a look at the date time settings on the computers. Pay close attention to t...

VBA Filter (with many variables) and copy result to new Worksheets
Using XL 2003 and 97 Two columns (fields) to be used to filter For Data set #1: ColumnA Division (Choices) A B C ColumnB Emp# (Choices are numbers and ALL need to be considered at once) From 4142 to 4143 From 4500 to 5001 From 4509 to 4512 From 6000 to 7000 4122, 4125, 4161, 4177 4803 I do know how to VBA-code a two column (field) filter. That said, how do I handle the multiple ColumnB possibilities? In reality, there are 23 more Emp#'s than I listed above. I know that there is a limit (7?) to variables includible in the AND and OR functions. There ar...

Difference in behavior or ProjectPercentCompleted and ProjectPercentWorkCompleted?
I'm sure of the implications, but not sure about how these two attributes actually differ from each other in behavior in project server. Are they basically the same? When would you use one column over the other in the reporting database? Thanks, Andy Novak UNT Andy -- These two fields correspond with the Percent Complete field and the Percent Work Complete fields you see in Microsoft Project. Percent Complete refers to the percentage of the Duration completed for a task or for the project, and is calculated using the formula: Percent Complete = Actual Duration/D...

My linked email id pages each looks different.
I have three linked email ids - xxx1@msn.com, xxx2@msn.com and xxx3@msn.com. xxx1 is the primary email I use. On the Windows Live page for this account (and for xxx3), the functions right above the email list says: New | Delete Junk | Mark as Move to | Print Icon On the xxx2 account, the functions right aboe the email list says: [Check box] View: All | Unread | From contacts | Social updates | From groups | Everything else << [Arrange by drop down] I can't figure out how to get my primary (and tertiary) email addre...

Evaluate a Range
I would like to create a formula that looks at a range with a specified criterion and when it finds the first entry in that range that meets that criterion returns the position of that data in the range. For example, given the following data beginning in A1 and extending to D1: 5,7 ,8,11 I would like to create a formula that looks for a value greater than 10 in that range and then returns the number 4 to signify that it is the fourth entry in that range. Thanks as always for your help. --- Message posted from http://www.ExcelForum.com/ One way: Array enter (CTRL-SHIFT-ENTER or CMD-RET...

Football Results and Tables
i run a football league and would like to know if there is any formulas i could use for when i put a score in a cell it automatically sorts the table out to go with it. Many thanks Benn ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...

cells evaluation question
Hey I have a spreadsheet with 9 cells (c14:c22) They will have integers entered from 0 to 10 I need a formula to do the following: - Find how many have a value >=5 - Get the total value of the cells with a value >=5 Thanks Simon You want Countif and Sumif, as in: =countif(c14:c22,">=5") =sumif(c14:c22,">=5") Regards, Fred "Simon" <Simon@discussions.microsoft.com> wrote in message news:8D2F8EA3-D774-4A65-9157-274051900E28@microsoft.com... > Hey > > I have a spreadsheet with 9 cells (c14:c22) > They...

Two Instance of GP with Different SQL Colations
Hi, I have installed two instance of GP pointing to two different SQL Collations The problem I facing is, One Colation is Arabic Binary and another is 1256CI_AS. When I change my regional settings to English, then I am getting problems in another GP with different colation settings. Please help me to sort out this issue ASAP A SQL Server collation defines how the database engine stores and operates on character and Unicode data. After data has been moved into an application (GP in this case), however, character sorts and comparisons done in the application are controlled by the Windows lo...

Does IF() Evaluate in Pivot Table?
Is this function really evaluated in the calculated field of Pivo Table? IF(SALES > 10000,100,200) It always returns the value of the FALSE argument for the entir column -- Message posted from http://www.ExcelForum.com ...

lookup data in a different workbook
I export a list of items from Quickbooks. Quickbooks will export as a xls file. I want to create a different worksheet or workbook that will look u data in this file. I need to find data in a certain column in a certai row. So when my data changes in Quickbooks, I export the file, and the in another workbook or worksheet, update, based on the new or update data. I have tried the lookup wizard but it will not let me use different worksheet or workbook. I can make it work in the sam worksheet. So what I'm looking for is a direction where to start. I have made man complex Excel worksheets, ...

2002 evaluation.
Some time ago I ordered an evaluation copy of MS Publisher 2002. The CD came 2 days later, but that's when my hopes for a good evaluation ended. The thing won't install without a Product Key, which Microsoft never provided me with. During the installation I click on the appropriate link, but all I get is a polite info that the Key has been already sent to the e-mail provided, but will be re-sent. Guess what, it never came. I sent an e-mail to MS support asking for help. They replyed promptly with a car salesman like attitude, saying that they are dedicated to providing ...

Results from SQL database
Hi, I want to create a new search page which users will use to search for particular information from MS SQL database. How do i go about creating the search form and the results form. thanks for your help Mo What scripting language do you intend to use? If asp.NET the use Visual Web Developer Express instead of FrontPage, and look at the tutorials at www.HomePagedoctor.com/Tutorials.htm . Consider using Expression Web for designing the page layout. VWDE is a free download from http://www.asp.net/downloads/essential/ and a 60 day trial for Expression Web from http:...