Hard coded number range into a function

**bigdavediode** · 04-10-2009, 02:32 AM

Hi All,

I'm trying to do something which is probably painfully simple but I haven't been able to find the syntax anywhere. All I need is to use the forecast function =FORECAST(I902,F832:F901,I832:I901) but want to substitute a hard coded from and to value list, such as something like =forecast(71,F832:f901,{1-70})

That is, forecast case 71 given a range of 1 through 70. What is the correct syntax to give a from and to value? Curly brackets don't seem to work, and I don't want to do a list like {1,2,3,4,5,6,7...} which would be a pain.

Any ideas?

Thanks in advance for your help on this,
Bigdavediode

**DonkeyOte** · 04-10-2009, 02:49 AM

Not entirely sure I follow... not a function I use myself I confess but I think the data points of the latter range must match that of the former...

=FORECAST(71,F832:F901,ROW(INDEX(F832:F901,0))-(ROW(F832)-1))

**bigdavediode** · 04-10-2009, 10:50 AM

Thanks for your reply --

Forecast accepts arrays as the last two arguments, so I'm just trying to give an array of numbers. The second input listed would give one row minus another row, giving an integer, would it not?

I need to know how to express a series of numbers as an array: [1..70] {1..70}? I can't find the right format. Every time I search Google returns information on array functions.

Help!

**DonkeyOte** · 04-10-2009, 10:53 AM

this:

ROW(INDEX(F832:F901,0))-(ROW(F832)-1)

in the context of the FORECAST formula would generate an array of values {1 to 70}

To test / prove / visualise it ... put the above into the formula bar (with = obviously) and hit F9

**shg** · 04-10-2009, 10:56 AM

Perhaps use TREND instead, which allows you to omit the x values:

=TREND(71, F832:F901)

**bigdavediode** · 04-10-2009, 11:24 AM

Wow, you guys are fast! Thanks again!

Donkeyote, it's an extremely clever formula! And it works great, although I'm not sure I understand it despite reading up on index. But it does solve my problem!

But I'm still inconsolable as I don't know how to express an array of numbers. Is it curly brackets? Square brackets?

**DonkeyOte** · 04-10-2009, 11:29 AM

Re: inline array constant - you were correct at the beginning of your thread... ie: {1,2,3,4....70}

**bigdavediode** · 04-10-2009, 11:45 AM

Donkeyote -- thanks! Is it four dots, ie {1....70} ?

I just couldn't get that into the formula previously.

**DonkeyOte** · 04-10-2009, 11:46 AM

No ... the dots were meant to represent the fact that you would need to manually type all integers from 1 through 70 into your inline array constant as you had indicated in your first post... if you could do it shorthand wouldn't we have done it ?

**bigdavediode** · 04-10-2009, 12:09 PM

Thanks once again Donkey.

What an odd omission from Excel!

Hard coded number range into a function

LinkBack

Thread Tools

Rate This Thread

Display

Hard coded number range into a function

Re: Hard coded number range into a function

Re: Hard coded number range into a function

Re: Hard coded number range into a function

Re: Hard coded number range into a function

Re: Hard coded number range into a function

Re: Hard coded number range into a function

Re: Hard coded number range into a function

Re: Hard coded number range into a function

Re: Hard coded number range into a function

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions