Some formula results will not show up in the cell

Sometimes when I build a nested formula, the results don't show in the
cell. The only thing that shows is representative how the cell is formated,
ie: number format with 2 decimal points displays "0.00"

When I click on the cell with the formula, it appears in the input box. If
I click the equal sign, the drop-down menu shows the correct results, even if
I change the data it uses to determine the answer.

=AVERAGE(IF(A1:A12="Hours",C1:C12))

This is the formula. I intend to extend the range from 12 to about 90 when
it works.

Basically, what I want is to check for the word "Hours" from column A and
if it finds it, average the data from the corresponcing column C.

Since "Hours" is every 4th line, I was looking for a "step" function
simular to the one used in BASIC.

Any ideas?

Hi!

=AVERAGE(IF(A1:A12="Hours",C1:C12))

This is an array formula. Instead of just typing in the formula and then
hitting the ENTER key you must use the key combination of CTRL,SHIFT,ENTER.

Try this:

Select the cell this formula is in.

Now, goto the formula bar and place the cursor at the end of the formula.

Hold down the CTRL and SHIFT keys then hit ENTER.

When done properly Excel will place squiggly braces { } around the formula.
You must use the key combo to do this. You cannot just type the braces in
manually.

Biff

"Space Elf" <Space [email protected]> wrote in message
news:[email protected]...
> Sometimes when I build a nested formula, the results don't show in the
> cell. The only thing that shows is representative how the cell is
> formated,
> ie: number format with 2 decimal points displays "0.00"
>
> When I click on the cell with the formula, it appears in the input box.
> If
> I click the equal sign, the drop-down menu shows the correct results, even
> if
> I change the data it uses to determine the answer.
>
> =AVERAGE(IF(A1:A12="Hours",C1:C12))
>
> This is the formula. I intend to extend the range from 12 to about 90
> when
> it works.
>
> Basically, what I want is to check for the word "Hours" from column A and
> if it finds it, average the data from the corresponcing column C.
>
> Since "Hours" is every 4th line, I was looking for a "step" function
> simular to the one used in BASIC.
>
> Any ideas?
>

Thanks Biff! That did it!

"Biff" wrote:

> Hi!
>
> =AVERAGE(IF(A1:A12="Hours",C1:C12))
>
> This is an array formula. Instead of just typing in the formula and then
> hitting the ENTER key you must use the key combination of CTRL,SHIFT,ENTER.
>
> Try this:
>
> Select the cell this formula is in.
>
> Now, goto the formula bar and place the cursor at the end of the formula.
>
> Hold down the CTRL and SHIFT keys then hit ENTER.
>
> When done properly Excel will place squiggly braces { } around the formula.
> You must use the key combo to do this. You cannot just type the braces in
> manually.
>
> Biff
>
> "Space Elf" <Space [email protected]> wrote in message
> news:[email protected]...
> > Sometimes when I build a nested formula, the results don't show in the
> > cell. The only thing that shows is representative how the cell is
> > formated,
> > ie: number format with 2 decimal points displays "0.00"
> >
> > When I click on the cell with the formula, it appears in the input box.
> > If
> > I click the equal sign, the drop-down menu shows the correct results, even
> > if
> > I change the data it uses to determine the answer.
> >
> > =AVERAGE(IF(A1:A12="Hours",C1:C12))
> >
> > This is the formula. I intend to extend the range from 12 to about 90
> > when
> > it works.
> >
> > Basically, what I want is to check for the word "Hours" from column A and
> > if it finds it, average the data from the corresponcing column C.
> >
> > Since "Hours" is every 4th line, I was looking for a "step" function
> > simular to the one used in BASIC.
> >
> > Any ideas?
> >
>
>
>

You're welcome. Thanks for the feedback!

Biff

"Space Elf" <[email protected]> wrote in message
news:[email protected]...
> Thanks Biff! That did it!
>
> "Biff" wrote:
>
>> Hi!
>>
>> =AVERAGE(IF(A1:A12="Hours",C1:C12))
>>
>> This is an array formula. Instead of just typing in the formula and then
>> hitting the ENTER key you must use the key combination of
>> CTRL,SHIFT,ENTER.
>>
>> Try this:
>>
>> Select the cell this formula is in.
>>
>> Now, goto the formula bar and place the cursor at the end of the formula.
>>
>> Hold down the CTRL and SHIFT keys then hit ENTER.
>>
>> When done properly Excel will place squiggly braces { } around the
>> formula.
>> You must use the key combo to do this. You cannot just type the braces in
>> manually.
>>
>> Biff
>>
>> "Space Elf" <Space [email protected]> wrote in message
>> news:[email protected]...
>> > Sometimes when I build a nested formula, the results don't show in the
>> > cell. The only thing that shows is representative how the cell is
>> > formated,
>> > ie: number format with 2 decimal points displays "0.00"
>> >
>> > When I click on the cell with the formula, it appears in the input
>> > box.
>> > If
>> > I click the equal sign, the drop-down menu shows the correct results,
>> > even
>> > if
>> > I change the data it uses to determine the answer.
>> >
>> > =AVERAGE(IF(A1:A12="Hours",C1:C12))
>> >
>> > This is the formula. I intend to extend the range from 12 to about 90
>> > when
>> > it works.
>> >
>> > Basically, what I want is to check for the word "Hours" from column A
>> > and
>> > if it finds it, average the data from the corresponcing column C.
>> >
>> > Since "Hours" is every 4th line, I was looking for a "step" function
>> > simular to the one used in BASIC.
>> >
>> > Any ideas?
>> >
>>
>>
>>