struggling with if logical test function

[neuromoe]

I have an Excel 2007 worksheet that is being used as a travel expense form
where I work. I have inherited this form, and have been charged with
updating it. There is a summary section at the bottom of the form that is
supposed to pull various account information from all over the page. The
formula that is currently in the cell is this:

=IF(H38=0,"
",$O$48&","&$H$38&","&M49&","&$F$38&","&$L$38&","&"0"&","&$J$41&","&$J$38)

which results in an account string, such as:

6450, 1005, 648001, 501205,,0,CR,3005

in other words, the information from cells O48, H38, M49, F38, L38, some
fields I don't get, J41, and J38.


I now need the result in this cell to look like this:

6450, 1005, 3005, 501205, CR, 648001

in other words, the information from cells O48, H38, J38, F38, J41, and M49
- eliminating a few fields from the old string and changing the order.


Honestly, I have no idea what I'm looking at when I look at the formula. I
understand the principle that my formula is set up to always test false, but
I don't know how to read the value_if_false statement in order to know how to
change the order of the result and eliminate some of the fields.

I know this is kind of a silly question, and I'm not sure anyone can help
based on what I've given you here, but at the very least, can someone point
me toward a good resource about how to read the syntax? I just don't know
what the ", &, and commas mean, and of course, I'm supposed to have this
changed asap.

Thank you!

 

[Kassie]

The &","& merely inserts a comma between two values.
So, $O$48&","&$H$38 inserts the value of O48, a comma, the value of H38. If
O48 contains 6450, and H38 contains 1005, you will see 6450,1005. to get
6450, 1005 you need to change your formula to $O$48&", "&$H$38, which will
insert not only a comma, but also a space, between the two amounts. To
change the order, you therefore have to change the order in which you refer
to the cells.

So, =IF(H38=0,"",$O$48&", "&$H$38&", "&", "&$J$38&", "&$F$38&", "&$J$41&",
"&$M$49) will do the trick

--
HTH

Kassie

Replace xxx with hotmail

 

[Shane Devenshire]

Hi,

Here is one solution

=IF(H38,$O$48&","&$H$38&","&$J$38&","&$F$38&","&$L$38&","&M49,"")

If you are not copying this formula you can simplify it to

=IF(H38,O48&","&H38&","&J38&","&F38&","&L38&","&M49,"")