Array Formula Not Correct

Close, but no cigar!

Row 5 contains Heading Information.
Row 6 contains Numbers

The array formula below only gives me the occurrence of a number in K6. If
K6 is zero, then I get "False".

The result should be something like this:
"Result is as follows...Detail: Hotel=$150.00, Gasoline=$75.00,
Total=$225.00

=IF(SUM(K6:S6)=0,J6,J6&".Detail:
"&IF(K6:S6<>0,K5:S5&"="&TEXT(K6:S6,"$#,##0.00")&IF(SUM(K6:S6)>0,"
,Total="&TEXT(SUM(K6:S6),"$#,##0.00"))))

I think you have to give a detailed explanation what you have and what you
want, it's clear that the formula you posted is incorrect

--
Regards,

Peo Sjoblom

(No private emails please)


"bw" <iamnu@cableone.net> wrote in message
news:11mi6lu5p69pdad@corp.supernews.com...
> Close, but no cigar!
>
> Row 5 contains Heading Information.
> Row 6 contains Numbers
>
> The array formula below only gives me the occurrence of a number in K6.
> If K6 is zero, then I get "False".
>
> The result should be something like this:
> "Result is as follows...Detail: Hotel=$150.00, Gasoline=$75.00,
> Total=$225.00
>
> =IF(SUM(K6:S6)=0,J6,J6&".Detail:
> "&IF(K6:S6<>0,K5:S5&"="&TEXT(K6:S6,"$#,##0.00")&IF(SUM(K6:S6)>0,"
> ,Total="&TEXT(SUM(K6:S6),"$#,##0.00"))))
>

I appreciate your reply. And I agree, I have done a poor job of explaining
the problem. Let me try again. Please ignore everything I have written
previously.

Cells in Row 1 contain Header Information
Cells in Row 2 contain Numeric data.

The two cells in each column are a pair, for example, A1 and A2 are a pair,
B1 and B2 are a pair, etc.

I want a formula that will concatenate all pairs to a single cell, and
provide a total at the end, of all Row 2 values, ONLY if the row 2 value of
the pair is Non-Zero.

For example, suppose all cells in row 2 are NON-ZERO.
Then the "equation" would be something like: =(A1&A2) & (B1 & B2) & (C1 &
C2).....& Sum(A2:x2). x=last column in the list.

But if Cell B2 was equal to Zero, then the formula above would be:
=(A1&A2) & (C1 & C2)....& Sum(A2:x2). NOTE: Column B is not included in
the formula because B2 is zero.

I think that explains it okay. Please let me know if you need additional
information.

Thanks,
Bernie



"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:OvGGlEC4FHA.2816@tk2msftngp13.phx.gbl...
>I think you have to give a detailed explanation what you have and what you
>want, it's clear that the formula you posted is incorrect
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "bw" <iamnu@cableone.net> wrote in message
> news:11mi6lu5p69pdad@corp.supernews.com...
>> Close, but no cigar!
>>
>> Row 5 contains Heading Information.
>> Row 6 contains Numbers
>>
>> The array formula below only gives me the occurrence of a number in K6.
>> If K6 is zero, then I get "False".
>>
>> The result should be something like this:
>> "Result is as follows...Detail: Hotel=$150.00, Gasoline=$75.00,
>> Total=$225.00
>>
>> =IF(SUM(K6:S6)=0,J6,J6&".Detail:
>> "&IF(K6:S6<>0,K5:S5&"="&TEXT(K6:S6,"$#,##0.00")&IF(SUM(K6:S6)>0,"
>> ,Total="&TEXT(SUM(K6:S6),"$#,##0.00"))))
>>
>

The best way might be VBA but if the range is not too big you can use brute
force

=IF(A2<>"",A1&" "&TEXT(A2,"$#,##0.00"),"")&" "&IF(B2<>"",B1&"
"&TEXT(B2,"$#,##0.00"),"")&" "&IF(C2<>"",C1&" "&TEXT(C2,"$#,##0.00"),"")&"
"&IF(D2<>"",D1&" "&TEXT(D2,"$#,##0.00"),"")&" "&IF(E2<>"",E1&"
"&TEXT(E2,"$#,##0.00"),"")&" "&IF(F2<>"",F1&" "&TEXT(F2,"V"),"")&"
"&TEXT(SUM(A2:F2),"$#,##0.00")

works from A to F

eventually you will reach the size limit when it comes to characters in a
formula



--
Regards,

Peo Sjoblom

(No private emails please)


"bw" <iamnu@cableone.net> wrote in message
news:11mj0im5toeigb1@corp.supernews.com...
>
> I appreciate your reply. And I agree, I have done a poor job of
> explaining the problem. Let me try again. Please ignore everything I
> have written previously.
>
> Cells in Row 1 contain Header Information
> Cells in Row 2 contain Numeric data.
>
> The two cells in each column are a pair, for example, A1 and A2 are a
> pair, B1 and B2 are a pair, etc.
>
> I want a formula that will concatenate all pairs to a single cell, and
> provide a total at the end, of all Row 2 values, ONLY if the row 2 value
> of the pair is Non-Zero.
>
> For example, suppose all cells in row 2 are NON-ZERO.
> Then the "equation" would be something like: =(A1&A2) & (B1 & B2) & (C1 &
> C2).....& Sum(A2:x2). x=last column in the list.
>
> But if Cell B2 was equal to Zero, then the formula above would be:
> =(A1&A2) & (C1 & C2)....& Sum(A2:x2). NOTE: Column B is not included
> in the formula because B2 is zero.
>
> I think that explains it okay. Please let me know if you need additional
> information.
>
> Thanks,
> Bernie
>
>
>
> "Peo Sjoblom" <terre08@mvps.org> wrote in message
> news:OvGGlEC4FHA.2816@tk2msftngp13.phx.gbl...
>>I think you have to give a detailed explanation what you have and what you
>>want, it's clear that the formula you posted is incorrect
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>> (No private emails please)
>>
>>
>> "bw" <iamnu@cableone.net> wrote in message
>> news:11mi6lu5p69pdad@corp.supernews.com...
>>> Close, but no cigar!
>>>
>>> Row 5 contains Heading Information.
>>> Row 6 contains Numbers
>>>
>>> The array formula below only gives me the occurrence of a number in K6.
>>> If K6 is zero, then I get "False".
>>>
>>> The result should be something like this:
>>> "Result is as follows...Detail: Hotel=$150.00, Gasoline=$75.00,
>>> Total=$225.00
>>>
>>> =IF(SUM(K6:S6)=0,J6,J6&".Detail:
>>> "&IF(K6:S6<>0,K5:S5&"="&TEXT(K6:S6,"$#,##0.00")&IF(SUM(K6:S6)>0,"
>>> ,Total="&TEXT(SUM(K6:S6),"$#,##0.00"))))
>>>
>>
>
>

Thanks Peo,
I have made some changes to your formula and it now works just fine. I have
not reached the size limit when it comes to characters, but I did reach the
limit on the number of "IF" statements. So I stopped at the limit, and then
used that cell as the first field to concatenate the remainder.

Since "The best way might be VBA", would you care to demonstrate?

Thanks again for your help,
Bernie


"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:uIlR3mC4FHA.1396@TK2MSFTNGP12.phx.gbl...
> The best way might be VBA but if the range is not too big you can use
> brute force
>
> =IF(A2<>"",A1&" "&TEXT(A2,"$#,##0.00"),"")&" "&IF(B2<>"",B1&"
> "&TEXT(B2,"$#,##0.00"),"")&" "&IF(C2<>"",C1&" "&TEXT(C2,"$#,##0.00"),"")&"
> "&IF(D2<>"",D1&" "&TEXT(D2,"$#,##0.00"),"")&" "&IF(E2<>"",E1&"
> "&TEXT(E2,"$#,##0.00"),"")&" "&IF(F2<>"",F1&" "&TEXT(F2,"V"),"")&"
> "&TEXT(SUM(A2:F2),"$#,##0.00")
>
> works from A to F
>
> eventually you will reach the size limit when it comes to characters in a
> formula
>
>
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "bw" <iamnu@cableone.net> wrote in message
> news:11mj0im5toeigb1@corp.supernews.com...
>>
>> I appreciate your reply. And I agree, I have done a poor job of
>> explaining the problem. Let me try again. Please ignore everything I
>> have written previously.
>>
>> Cells in Row 1 contain Header Information
>> Cells in Row 2 contain Numeric data.
>>
>> The two cells in each column are a pair, for example, A1 and A2 are a
>> pair, B1 and B2 are a pair, etc.
>>
>> I want a formula that will concatenate all pairs to a single cell, and
>> provide a total at the end, of all Row 2 values, ONLY if the row 2 value
>> of the pair is Non-Zero.
>>
>> For example, suppose all cells in row 2 are NON-ZERO.
>> Then the "equation" would be something like: =(A1&A2) & (B1 & B2) & (C1 &
>> C2).....& Sum(A2:x2). x=last column in the list.
>>
>> But if Cell B2 was equal to Zero, then the formula above would be:
>> =(A1&A2) & (C1 & C2)....& Sum(A2:x2). NOTE: Column B is not included
>> in the formula because B2 is zero.
>>
>> I think that explains it okay. Please let me know if you need additional
>> information.
>>
>> Thanks,
>> Bernie
>>
>>
>>
>> "Peo Sjoblom" <terre08@mvps.org> wrote in message
>> news:OvGGlEC4FHA.2816@tk2msftngp13.phx.gbl...
>>>I think you have to give a detailed explanation what you have and what
>>>you want, it's clear that the formula you posted is incorrect
>>>
>>> --
>>> Regards,
>>>
>>> Peo Sjoblom
>>>
>>> (No private emails please)
>>>
>>>
>>> "bw" <iamnu@cableone.net> wrote in message
>>> news:11mi6lu5p69pdad@corp.supernews.com...
>>>> Close, but no cigar!
>>>>
>>>> Row 5 contains Heading Information.
>>>> Row 6 contains Numbers
>>>>
>>>> The array formula below only gives me the occurrence of a number in K6.
>>>> If K6 is zero, then I get "False".
>>>>
>>>> The result should be something like this:
>>>> "Result is as follows...Detail: Hotel=$150.00, Gasoline=$75.00,
>>>> Total=$225.00
>>>>
>>>> =IF(SUM(K6:S6)=0,J6,J6&".Detail:
>>>> "&IF(K6:S6<>0,K5:S5&"="&TEXT(K6:S6,"$#,##0.00")&IF(SUM(K6:S6)>0,"
>>>> ,Total="&TEXT(SUM(K6:S6),"$#,##0.00"))))
>>>>
>>>
>>
>>
>

One VBA solution in your post in excel.programming:
http://tinyurl.com/9f77b

Regards
Rowan

bw wrote:
> Thanks Peo,
> I have made some changes to your formula and it now works just fine. I have
> not reached the size limit when it comes to characters, but I did reach the
> limit on the number of "IF" statements. So I stopped at the limit, and then
> used that cell as the first field to concatenate the remainder.
>
> Since "The best way might be VBA", would you care to demonstrate?
>
> Thanks again for your help,
> Bernie
>
>