• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array Mixup

bobhc

bobhc

Excel Ninja
Good day

I am dipping my toes in to arrays and I have a problem in my worksheet on the desk top that does not show in the uploaded sheet. On my work sheet cell G6 shows the error #VALUE! and the values in the rest of the G column are dropped down one cell, G7 is 50, G8 is £250, G9 is £450, G10 is £1050, and the £300 for the Brass screws does not show. But when the file is upload all cells show as they should? I entered the following in to the selected cells (G) via the formula bar and used ctrl+shift+enter...{=D5:D10*F5:F10}. Could some one please point in the right direction and explain my fault or wrong input!

,

https://docs.google.com/spreadsheet/ccc?key=0AjSJrMR-QH5bdFBUc3B1OTEwRjM0MEMtZXdzMmZvNlE
 
Bobhc


Change G6 to a simple:
Code: 
=D6*F6

Then copy down


You could have used in G6 =D$6:D$10*F$6:F$10

But don't Array Enter it
 
Hi, b(ut)ob(ut)hc!


Have a good day, old dog new-fully-tricked.


I downloaded the workbook, I opened it, and the values in column G appear to respond to the multiplication of D cells by F cells. But, yes, but, there's no need to use an array formula here.


The G6 cell had the formula =D5:D10*F5:F10 (which if it was going to be copied down it should have been row fixed as =D$5:D$10*F$5:F$10), and G7 =D6:D11*F6:F11, and so on until G10 with =D10:D14*F10:F14.


I see them not as array formulas but as normal formulas as well. And the result displayed is the same as if I had written in G6 =D6*F6 and copied down thru G10.


A possible use of an array formula in this example might be defining G11 cell formula as ={SUM(D6:D10*F6:F10)}.


Here you have many good articles on this subject:

http://chandoo.org/wp/tag/array-formula/


Regards!
 
@b(ut)ob(ut)hc!

Hi!

Hui's definitively writes faster than me... unless at 2.42am...

Regards!
 
Good day Hui and SirJB7

I understand what you are say re not using an array for such a small table and you have both show alternative methods of achieving the end result, but I am no wiser as to why the array gives an error on my work sheet but uploads the correct result, I am just trying to understand (in a small way) the workings of an array.
 
Hi, b(ut)ob(ut)hc!

Not exactly or maybe I didn't express myself clearly. I intended to mean and said that using an array formula for cells G6:G10 doesn't make any sense. Yes it does makes sense using it for cell G11 only.

Regards!
 
Good day SirJB7, Have re-read the reply's from yourself and Hui and uploaded my small array, I think I am on the road of discovery re arrays, I have used Dropbox as it seem Google makes unscripted changes to uploads.

,

http://dl.dropbox.com/u/75495784/Array1.xlsx
 
Hi, b(ut)ob(ut)hc!

I saw your new file and I noticed that you corrected the range so as to exclude titles (right), that you still use array formulas for J5:J9 (unnecessary, remember that only references for active row take place, it's the same if you write =G5*I5 and copy down) and that you still use an array formula for J10 subtotal (right).

Go on discovering and come back whenever needed or wanted, old dog new-fully-tricked.

Regards!
 
Good day Montrey

At this moment I have one shoe off, one sock off and have my big toe in the water, it will be some time before I will be able to dive into Index arrays and sumproduct arrays
 
@b(ut)ob(ut)hc

Hi!

Well, start diving into the water with your big toe. Then, when you're ready, dive with all the rest of the foot, the the leg, and so on...

Regards!
 
SirJB7...Read your post and of to buy a pair of water wings, then released I have some already...called Chandoo.org
 
You must log in or register to reply here.
Back
Top