You're welcome. One thing that Walkenbach teaches in that book is how to
deal with large, complex formulas such as you had. Basically it falls back
to the old school thought of 'if a problem is so complex you can't get a
grasp of it, break it down into smaller pieces that you can deal with
effectively'. Once you've done that you can start putting the smaller pieces
into a larger component and repeat until you have a really ugly, complex
beast that you'll probably never be able to understand again in your entire
lifetime! Well, that's the way I feel about some that I end up with out of
such things. But some (most, I think) folks are better with worksheet
formulas than I am anyhow.
"Hell-fire" wrote:
> Thank you JLatham
>
> "JLatham" wrote:
>
> > I'll second the vote for Walkenbach's book that Bob Phillips has recommended.
> > It reads well, the formulas work, there's a CD with all of it on it, and
> > much of what is presented in the book actually has a practical application in
> > the real world.
> >
> > "Hell-fire" wrote:
> >
> > > Hi JLatham,
> > >
> > > Thank you for the suggestion, I will try it out and see how well it works.
> > > By the way, do you have any suggestions on reading material that can help me
> > > understand how to make formulas for Excel, so I don't have to keep bothering
> > > everyone in the Newsgroup? Thank you
> > >
> > >
> > > "JLatham" wrote:
> > >
> > > > Roger may come back and figure a better way - but I think this version of it
> > > > will work to do what you want: with 1.1 you get like "1F", but with 1.0 you
> > > > just get "F"
> > > >
> > > > =IF(AX8=1,IF(OR(AF8="A",AF8="V"),
> > > > IF(AZ8="?",IF(BA8="-","F","B"),IF(BA8="?","I","X")),""),IF(AX8=1.1,IF(OR(AF8="A",AF8="V"),"1" & IF(AZ8="?",IF(BA8="-","F","B"),IF(BA8="?","I","X")),""),""))
> > > >
> > > >
> > > > "Hell-fire" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I need to edit a formula and don't really know how to do it. This was made
> > > > > by another person which is no longer here. I'll try my best to explain what
> > > > > it does.
> > > > >
> > > > > This forumla interacts with 9 other cells, the default setting in column C is:
> > > > > XxxxxX###_xxxx_XXXxxxxx where column C = Column D # is an ID number.
> > > > > For example F0705V363_GML_MagAPACS
> > > > >
> > > > > Column D =CONCATENATE(E#,F#,G#,H#,L#,I#,L#,J#,K#) # is the row number
> > > > > Column E =IF(AZ#="○", IF(BA#="-","F","B"),IF(BA#="○","I","X"))
> > > > > Column F =IF(S#>0, CONCATENATE(0,LEFT(S#,3)),"xxxx")
> > > > > Column G =IF(AF#="A","A",IF(AF#="V","V","X"))
> > > > > Column H =B#
> > > > > Column I =IF(ISBLANK(AG#)=TRUE,"xxxx",AG#)
> > > > > Column J =IF(ISBLANK(AM#)=TRUE,"xxx",LEFT(AM#,3))
> > > > > Column K =IF(ISBLANK(AP#)=TRUE,"xxxxx"Left(AP#,5))
> > > > > Column L =_
> > > > >
> > > > > Column B has ID numbers in it.
> > > > >
> > > > > Now what I need to do is add Column AX in this formula that takes values
> > > > > from both AX and AF. When AX has value of 1.1 and AF has either A or V, I
> > > > > need Column E to respond with a 1F or 1B, instead of just F or B. But when
> > > > > AX has value of 1.0, I need F or B returned.
> > > > >
> > > > > Roger Govier was kind enough to suggest this formula:
> > > > >
> > > > > =IF(AND(AX8=1.1,OR(AF8="A",AF8="V")),"1"&IF(AZ8="?",
> > > > > IF(BA8="-","F","B"),IF(BA8="?","I","X")),"")
> > > > >
> > > > > It works fine if I have AX value of 1.1, but when I have value 1.0, I loose
> > > > > my F or B. I tried to understand how this works, but unfortunately I don't.
> > > > > So if anyone can help me, please do and I thank anyone that willing to take a
> > > > > look at this.
> > > > >
> > > > > I hope I explained this well enough.
> > > > >
> > > > >
Similar Threads
