cell checking

[Lee]

I have two Sheets (sheet1,sheet2), Sheet 1 has corridinate data imported from
Autocad. On Sheet 2, cells B57 through B76 have IF statements in them for
calculating data from Sheet 1.

What I want to do is check if cell B76 equals 0. If the cell equals 0 then
go to cell b75 and check if it equals 0 and so on. When it gets to the first
cell that is greater than 0 do a calculation that would return a value to
cell B77, and stop checking.

Is this possible without using VBA programming ?

Thanks for your input

[Bernie Deitrick]

Lee,

In cell B77, use the array formula:

=INDEX(B57:B76,MAX((B57:B76>0)*ROW(B57:B76))-ROW($B$57)+1)

Entered with Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP


"Lee" <[email protected]> wrote in message
news:[email protected]...
>I have two Sheets (sheet1,sheet2), Sheet 1 has corridinate data imported from
> Autocad. On Sheet 2, cells B57 through B76 have IF statements in them for
> calculating data from Sheet 1.
>
> What I want to do is check if cell B76 equals 0. If the cell equals 0 then
> go to cell b75 and check if it equals 0 and so on. When it gets to the first
> cell that is greater than 0 do a calculation that would return a value to
> cell B77, and stop checking.
>
> Is this possible without using VBA programming ?
>
> Thanks for your input
>
>

[Lee]

what do you mean by "Entered with Ctrl-Shift-Enter."


"Bernie Deitrick" wrote:

> Lee,
>
> In cell B77, use the array formula:
>
> =INDEX(B57:B76,MAX((B57:B76>0)*ROW(B57:B76))-ROW($B$57)+1)
>
> Entered with Ctrl-Shift-Enter.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Lee" <[email protected]> wrote in message
> news:[email protected]...
> >I have two Sheets (sheet1,sheet2), Sheet 1 has corridinate data imported from
> > Autocad. On Sheet 2, cells B57 through B76 have IF statements in them for
> > calculating data from Sheet 1.
> >
> > What I want to do is check if cell B76 equals 0. If the cell equals 0 then
> > go to cell b75 and check if it equals 0 and so on. When it gets to the first
> > cell that is greater than 0 do a calculation that would return a value to
> > cell B77, and stop checking.
> >
> > Is this possible without using VBA programming ?
> >
> > Thanks for your input
> >
> >
>
>
>

[Dave Peterson]

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Lee wrote:
>
> what do you mean by "Entered with Ctrl-Shift-Enter."
>
> "Bernie Deitrick" wrote:
>
> > Lee,
> >
> > In cell B77, use the array formula:
> >
> > =INDEX(B57:B76,MAX((B57:B76>0)*ROW(B57:B76))-ROW($B$57)+1)
> >
> > Entered with Ctrl-Shift-Enter.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Lee" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have two Sheets (sheet1,sheet2), Sheet 1 has corridinate data imported from
> > > Autocad. On Sheet 2, cells B57 through B76 have IF statements in them for
> > > calculating data from Sheet 1.
> > >
> > > What I want to do is check if cell B76 equals 0. If the cell equals 0 then
> > > go to cell b75 and check if it equals 0 and so on. When it gets to the first
> > > cell that is greater than 0 do a calculation that would return a value to
> > > cell B77, and stop checking.
> > >
> > > Is this possible without using VBA programming ?
> > >
> > > Thanks for your input
> > >
> > >
> >
> >
> >

--

Dave Peterson

[Bernie Deitrick]

Lee,

Instead of typing in the formula and pressing enter, type in the formula, hold down both the ctrl
and shift buttons, and while they are help down, press enter. Just like Ctrl-Alt-Delete.... That
tells Exel to process the formula differently.

HTH,
Bernie
MS Excel MVP


"Lee" <[email protected]> wrote in message
news:[email protected]...
> what do you mean by "Entered with Ctrl-Shift-Enter."
>
>
> "Bernie Deitrick" wrote:
>
>> Lee,
>>
>> In cell B77, use the array formula:
>>
>> =INDEX(B57:B76,MAX((B57:B76>0)*ROW(B57:B76))-ROW($B$57)+1)
>>
>> Entered with Ctrl-Shift-Enter.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Lee" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have two Sheets (sheet1,sheet2), Sheet 1 has corridinate data imported from
>> > Autocad. On Sheet 2, cells B57 through B76 have IF statements in them for
>> > calculating data from Sheet 1.
>> >
>> > What I want to do is check if cell B76 equals 0. If the cell equals 0 then
>> > go to cell b75 and check if it equals 0 and so on. When it gets to the first
>> > cell that is greater than 0 do a calculation that would return a value to
>> > cell B77, and stop checking.
>> >
>> > Is this possible without using VBA programming ?
>> >
>> > Thanks for your input
>> >
>> >
>>
>>
>>

[Lee]

Thanks alot you guys,

Learn something new every day

"Bernie Deitrick" wrote:

> Lee,
>
> Instead of typing in the formula and pressing enter, type in the formula, hold down both the ctrl
> and shift buttons, and while they are help down, press enter. Just like Ctrl-Alt-Delete.... That
> tells Exel to process the formula differently.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Lee" <[email protected]> wrote in message
> news:[email protected]...
> > what do you mean by "Entered with Ctrl-Shift-Enter."
> >
> >
> > "Bernie Deitrick" wrote:
> >
> >> Lee,
> >>
> >> In cell B77, use the array formula:
> >>
> >> =INDEX(B57:B76,MAX((B57:B76>0)*ROW(B57:B76))-ROW($B$57)+1)
> >>
> >> Entered with Ctrl-Shift-Enter.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Lee" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have two Sheets (sheet1,sheet2), Sheet 1 has corridinate data imported from
> >> > Autocad. On Sheet 2, cells B57 through B76 have IF statements in them for
> >> > calculating data from Sheet 1.
> >> >
> >> > What I want to do is check if cell B76 equals 0. If the cell equals 0 then
> >> > go to cell b75 and check if it equals 0 and so on. When it gets to the first
> >> > cell that is greater than 0 do a calculation that would return a value to
> >> > cell B77, and stop checking.
> >> >
> >> > Is this possible without using VBA programming ?
> >> >
> >> > Thanks for your input
> >> >
> >> >
> >>
> >>
> >>
>
>
>