semicolon and comma in formula's
Hi,
It's a well-known fact that Excel 2010 has a problem in the use of semicolons and comma's for defining a range due to regional settings. Changing these settings doesn't resolve everything, however. What if you have to combine comma's and semicolons?
My example:
I need to use this formula:
=(GROWTH(G25:G33,AH3:AH14;B25:B33,AC3:AC14;AC15))
G25:G33,AH3:AH14 and B25:B33,AC3:AC14 here are non-adjacent cell-ranges, while the semicolons separate known y's, known x's and new x.
No way to have excel accept this... It will take semicolons or comma's as separators, but never both. Selecting ranges by mouse is no option, it will automaticaly use semicolons or comma's.
Who can help me?
| Attachment | Size |
|---|---|
| screenshot.png | 50.29 KB |
I don't think you can combine different separators, but anyway..
Why do you need to combine different separators anyway?
Well, as in my example, I
Well, as in my example, I need a separator for the non-adjacent cell-ranges and a separator for the different parameters of the formula. What happens now is that excel interpretes the first separator (non adjacent-range) as a separator for a new formula parameter.
In other words: it seems not to be possible to use a non-adjacent range in a formula with different parameters.
I guess it is due tu particular function.
Can your function accept non-adjacent ranges as parameters?
Just saw there is a
Just saw there is a parenthesis missing in Pics 4 and 5, but this does not influence the outcome, I tried it. (Corrected it in the pic now)
Screenshots
Maybe my example is a bit complex, so I simplified it and added some screenshots for better understanding (I added the pics to the main topic, I didn't find out how to link pics to a comment):
Let's start with the situation in the first pic:
In B3 I want the expected growth of the series B1 to B2, related too the series A1 to A3.
So I add the formula in the second pic.
The third pic shows the correct result.
In pic 4 I reordened my cells: A1 ans B1 stay in place, A2 becomes C1, A3 becomes E1, B2 becomes D1 and B3 becomes F1.
To have the same result I have to add B1 and D1 as 'known y's', A1 and C1 as 'known x's' and E1 as 'new x'.
Pic 5 shows what happens ...
And this goes for every function that employs multiple parameters.