Array Formula Not Correct

[bw]

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

 

[Peo Sjoblom]

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]

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]

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]

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

 

[Rowan Drummond]

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

Regards
Rowan

 

[bw]

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]

You're welcome.