Cell Range

[Altec101]

Hello,

How can I have a formula end at a certain range of cells without knowing that range of cells ahead of time?

I have a formula in cell B1, which checks numbers in cell A1, and based on the number in A1, the formula changes B1 to a letter.
I copied the formula down so far in the B1 range, but the length of data in cell A1 can be different each time.

Thanks

[Bob Phillips]

You would need to add some test to the formula. What is that formula, and
what sort of data in A1?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Altec101" <[email protected]> wrote in
message news:[email protected]...
>
> Hello,
>
> How can I have a formula end at a certain range of cells without
> knowing that range of cells ahead of time?
>
> I have a formula in cell B1, which checks numbers in cell A1, and based
> on the number in A1, the formula changes B1 to a letter.
> I copied the formula down so far in the B1 range, but the length of
> data in cell A1 can be different each time.
>
> Thanks
>
>
> --
> Altec101
> ------------------------------------------------------------------------
> Altec101's Profile:
http://www.excelforum.com/member.php...o&userid=34539
> View this thread: http://www.excelforum.com/showthread...hreadid=571898
>

[Altec101]

The data in A1 would look like this: 0:03:00


The formula is working great, it does what it needs to do.
This is the formula:

=IF(E2>=Buckets!$C$1,"A",IF(E2>=Buckets!$C$2,"B",IF(E2>=Buckets!$C$3,"C",IF(E2>=Buckets!$C$4,"D",IF(E2>=Buckets!$C$5,"E","NA")))))

[Bob Phillips]

You don't give much away do you?

I presume that if C6 has data you want the formula to test that, then C7,
etc.?

If so, then use

=INDEX(C1:C10,MIN(IF(E2<C1:C10,ROW(C1:C10))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

just change C1:C 10 to the maximum you will ever need.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Altec101" <[email protected]> wrote in
message news:[email protected]...
>
> The data in A1 would look like this: 0:03:00
>
>
> The formula is working great, it does what it needs to do.
> This is the formula:
>
>
=IF(E2>=Buckets!$C$1,"A",IF(E2>=Buckets!$C$2,"B",IF(E2>=Buckets!$C$3,"C",IF(
E2>=Buckets!$C$4,"D",IF(E2>=Buckets!$C$5,"E","NA")))))
>
>
> --
> Altec101
> ------------------------------------------------------------------------
> Altec101's Profile:
http://www.excelforum.com/member.php...o&userid=34539
> View this thread: http://www.excelforum.com/showthread...hreadid=571898
>

[Altec101]

The formula checks cell E2 on a sheet called stats to see if the number in that cell is between a 15 minute intervals which it gets from a sheet called Buckets on the same spreadsheet. On this page it has cells ranging from
C1, C2, C3, C4, and C5 which has numbers in them cells like this:
1:00:00
0:45:00
0:30:00
0:15:00
0:00:00

The cell that contains the formula then places an A, B, C, D, or E in that cell depending on the results.
I need the formula to only look at the amount of data copy over that month, which the amount of data will be different each month.

=IF(E2>=Buckets!$C$1,"A",IF(E2>=Buckets!$C$2,"B",I F(E2>=Buckets!$C$3,"C",IF(E2>=Buckets!$C$4,"D",IF(E2>=Buckets!$C$5,"E","NA") ))))