Click here for important information about Computing.net.

=('DB2011'!$E$28:$Z$28)*D28

I am trying to figure out how the utilize the above formula and have it sequncel in my spreadsheet. Once I have this done I also need it to be an array. I know how to make an individual row an array (ctrl+shift+enter) but how to make 4000 rows an array without doing each one at a time?

HI, It might be helpful if you posted the first two array formulas you want, each with the cell address.

For example

A1 {=('DB2011'!$E$28:$Z$28)*D28}

B1 {=('DB2011'!$E$28:$Z$28)*E28}

A2 {=('DB2011'!$E$29:$Z$29)*D29}

Knowing how you want your formulas to change (or not) as they are dragged horizontally or vertically will make it easier to advise on a solution.Note that array formulas can be dragged or copied and pasted like ordinary formulas, even after entering Ctr+Shift+Enter.

The key to how the formula extends is in the $ signs.

Regards

Humar {=('DB2011'!$E$28:$Z$28)*D28}

{=('DB2011'!$E$29:$Z$29)*D29}

{=('DB2011'!$E$30:$Z$30)*D30}It is the same formula but it should corresepond to the row number. I have tried removing a single dollar sign , but then dont know how to get it back in and make it an array

I entered this in A1: =('DB2011'!$E28:$Z28)*D28

I hit Ctrl-Shift-Enter and got this:

{=('DB2011'!$E28:$Z28)*D28}

I grabbed the plus sign and dragged it down and got this:

{=('DB2011'!$E29:$Z29)*D29}

{=('DB2011'!$E30:$Z30)*D30}Isn't that what you want?

The $ has nothing to do with whether a formula is an array formula or not. It is used to tell Excel whether or not to hold the Row and/or Column reference constant.

that is what i wanted , but how do you grab the plus sign ? and when do you put the plus sign and where?

Hi, When you select a cell and move your mouse over the cell, there is a small square in the bottom-right corner, and the cursor changes to a plus sign when it is over this small square.

At this point, with the cursor as a plus sign, over the bottom-right corner of the cell containing the formula, click the left mouse button and hold it down, and drag the cell in the required direction.

Release the mouse key when you have reached the required cell.

Your formula will now have been extended to that cell.

The way the formula changes as it is dragged to extend it depends on the $ signs in the cell addresses.

Regards

OK, I don't mean anything by this, but I gotta say that I'm amazed that someone sophisticated enough to be using array formulas doesn't know about dragging the plus sign to autofill cells. How have you autofilled formula and data in the past? That's as shocking as just finding out that my wife did not know that you could bookmark web pages in a browser. Granted, she doesn't surf the web as much as many of us do, but she never knew how easy it was to get back to a page she liked. She always searched (and stumbled) her way around until she found it.

Hi DerbyDad03 I just assumed that the OP had misunderstood your reply, rather than not actually knowing about how to drag and extend formulas.

But look at the formula - I just put it all on one worksheet for ease of testing: - In cell C28:

{=($E$28:$Z$28)*D28}

Then 3 in D28 and 2 in all cells E28 to Z28

The result of the array formula is 6

What is more interesting is that it appears to use only the value in E28.

Change E28 and the result changes.

Change Z28 and the result does not change.So I don't think that this array formula does anything useful and does not do anything that ordinary formula can't do.

So a couple of questions for mfeeder:

1. What are you trying to do with this formula;

are you for example trying to multiply the total of the cells E28 to Z28 on worksheet DB2011 by the value in D28

2. Why did you choose an array formulaRegards

Well first of all I really do appreciate all of your help!!

I am not a sophisticated person by any means (NO OFFENSE TAKEN) and I have been using this array worksheet that was created probabaly about 18 years ago.I thought it was about time to update the worksheet with current information for what it is used for, which is creating a database for keeping infomration in a constant fashion when copied and pasted into another sheet. So what I did was click on the cell and saw the formula, I used the training on line to work through some isues on how to create the formula and some short cuts to get it to the place I have it today . There are actaully 3999 rows of data and before now I was doing each row one at a time while of the time was looking online and asking people if there was a way to do it with copy and paste. I wrote a macro to make them all rows an absolute or relative, but found that I would still have to go row by row to elimInate the $ after the *D I was updating this work sheet not to do mathmatical formulas. This database used to be 3 sheets all linked. The first sheet was called the BOOK, the second sheet was the database 9where the info is pulled from , the third was a labor rate sheet. Each time you opened the bok it asked to be updated. I have now combined them all together in one excel database and have it all done, exepct this , so I do thank you for helping. I would send the whole book but i dont see where i can attach so you can see what the whole sheet looks like. I will try the dragging later today and report back.

Oh I forgot to answer the 1. question - Yes I am trying to mutlipy everything in that row by D28 and to make sure that the information contained in that row doesnt chnage no matter where i pasted the row. As for why I used this array , it was already exisiting and I didnt know another way to make sure the data didnt change.

Hi, The array formula does not work the way you want it to - it does not multiply everything on the row by D28.

Even if it did, there is no output to tell you that the data is unchanged.If you want a warning that the data in E28 to Z28 is the same as expected try this:

In D28 enter the expected sum of the values in E28 to Z28

In C28 enter this Formula:

=IF(SUM('DB2011'!E28:Z28)=D28,"OK","Data changed")

This is an ordinary formula, not an array formula.

It sums the values in cells E28 to Z28 on the worksheet named "DB2011" and compares the result to the value in cell D28. If it matches you see "OK" but if it doesn't match you see "Data changed"

However simple tests like this can be misleading because two or more changes could result in the same total.

To be sure that data has not changed you would need to use a check digit calculation, or some calculation that involves the position of the data as well as its absolute value.You could use the following array formula:

{=IF(SUM('DB2011'!E28:Z28*COLUMN('DB2011'!E28:Z28))=D28,"OK","Data changed")}

This multiplies the value in each cell E28 to Z28 by the column number of each cell, then it Sums the results and finally compares the total to the stored value in D28, returning OK or the Data changed message.

Transposing two values for example, will result in a different total from this array formula, so warning of a data change.You may wish to try this little exercise to help understand how $ signs affect formulas.

Note that the $ signs in formulas do not change the result of a formula =D28 returns the same value as =$D$28Enter X in cell A1, Y in cell B1 and Z in cell A2

Now go to any empty cell, say D5 and enter =A1

Drag the formula one column to the right, then go back to D5 and drag it down one row

The three cells return the values X, Y and Z and the formulas are =A1, =B1 and =A2

Now change D1 to =$A$1 and repeat the two drags.

and look at the results and formulas - all three cells display X and all three formulas are =$A$1

then do it again using =$A1

and finally =A$1

Here are the four results with the formula used shown in column C:C D E A1 X Y Z $A$1 X X X $A1 X X Z A$1 X Y XRegards

Ask Your Question

Weekly Poll

Do you think Intel's new hybrid chips will better compete with Apple/ARM?

Yes (14) | ||

No (14) | ||

I don't know (15) |

Discuss in The Lounge

Poll History