Array Formula Not Correct

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
>
>

I appreciate your help Rowan.

Wow, your solution works really well, and without all the IF statements. I
knew there was something out there like this, but I did such a poor job of
explaining what I wanted, it took Peo to drag it out of me.

All I have to do now is format the result the way I wanted and it will be
perfect.
Thanks a bunch,
Bernie

"Rowan Drummond" <rowanzsaNotThis@hotmail.com> wrote in message
news:%23Zr0qkD4FHA.3636@TK2MSFTNGP09.phx.gbl...
> 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
>>

You're welcome.

bw wrote:
> I appreciate your help Rowan.
>
> Wow, your solution works really well, and without all the IF statements. I
> knew there was something out there like this, but I did such a poor job of
> explaining what I wanted, it took Peo to drag it out of me.
>
> All I have to do now is format the result the way I wanted and it will be
> perfect.
> Thanks a bunch,
> Bernie
>