Include values of cells as an array in a formula ????

[spydey]

It has been a while since I posted.

I have a formula that references some values in a range. The range is static and the values are constant.

The formula is in cell E3.

I would like to remove the values from the range and hard code them into the formula so that I don't have to have the values visible in my data set.

Here is the formula, entered via CSE:

=IF(AND(F3:F6=0),"N/A",SUM(IF(D3:D6="N/A",0,D3:D6)*(C3:C6))/(100-SUM((D3:D6="N/A")*(C3:C6))))

The values are currently stored in the range C3:C6.

The values in vertical order are:

30
15
5
50

I tried to update the formula to the following (entered via CSE) but it does not give me the expected value.

=IF(AND(F3:F6=0),"N/A",SUM(IF(D3:D6="N/A",0,D3:D6)*{30,15,5,50})/(100-SUM((D3:D6="N/A")*{30,15,5,50})))

I have used similar formulas before and they work fine. I do not know why it does not want to calculate correctly.

Any pointers, ideas, thoughts, etc., are greatly appreciated.

-Spydey

 

[lrobbo314]

Does it work if you use semicolons instead of commas?

e.g. {30;15;5;50}

 

[spydey]

Does it work if you use semicolons instead of commas?

e.g. {30;15;5;50}

All of my other formulas where I do something similar, and they work, do use semi-colons.

However, I will give it a try.

Spydey

 

[spydey]

Sorry, meant to say they do not use semi-colons.

-Spydey

 

[spydey]

Ok, so I just tried it and it works now!!!

Interestingly, my other formulas all use commas, but they are referencing horizontal ranges.

Whereas this formula references a vertical range.

I wonder if there is a correlation between using semi-colons for vertical ranges and commas for horizontal ranges. Something I did not know before!!

Thanks @lrobbo314 for the heads up!! I appreciate it.

-Spydey

 

[lrobbo314]

No problemo.

You pretty much figured it out. When using arrays like this, commas act like delimiters for columns whereas semicolons act as row delimiters.