Re: Simple "If" Function - HELP

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Jun 18, 3:47 am, "GB" <NOTsome...@xxxxxxxxxxxxx> wrote:
"James8309" <jaedong1...@xxxxxxxxx> wrote in message

news:98914eb8-9963-4199-910e-4b2daf28b476@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx





Hi, Everyone

Let me first explain what I am trying to achieve very briefly.

1. If A1 = "E" and A2 >A3 and A3<A4 then return "ABC"
= > I know I can do this by using " if(and(A1="E",A2>A3,
A3>A4),"ABC",""))

However! However!!

2. if A1 = "E" stays same... except next criterias are different and
yes there are 95 of them. I just thought that it is painful to use
"And" function to hook up all the three criterias.
i.e. a) A1 = "E", A2<A3, A3=A4,  then return "DEF" etc etc
     b) A1 = "E", A2=A3, A4>A3 then return "XXX" etc etc etc etc x 95
times.

Is there any other way to lock that A1 = "E" ?? so I don't have to
type "And(A1 = "E", etc etc )" 95 times???

Should I be using VBA instead? Would that be easier?

Thank you for your help in advance!

Hmm, interesting. So, if I have understood you correctly, your cell will
contain 95 If statements all linked together with &s? You don't explain if
there is any logic in the way those ABCs and DEFs are generated. I can't
remember the maximum number of characters in a cell, but hopefully that
won't be a problem. However, actually generating the 95 If statements will
be (almost) impossible to do accurately by hand. I suggest that you create a
spreadsheet specifically to generate the formula you want to use.

An alternative is to use a scoring system, so A1 = A scores 1, through to
A1=Z scores 26. Also A2<A3 scores 100, A2= A3 scores 200, etc. (The scores
are done so that each combination is bound to give a unique value.) Then add
the 3 scores and use a lookup tables.- Hide quoted text -

- Show quoted text -


Firstly, Thanks for your help!


Basically,
1. I have Range A1 to A850 containing alphabets. They are almost
distributed.
i.e. A1 might be B, A2 might be Z ... etc

2. Column B, C, D and E contains numbers.

3. Each alphabet in Range("A1:A850") has different families.
i.e. A will have A110, A220, A33, A555 etc depending on relationship
between Column B,C,D and E

Example, lets say A group
A111 = B>C, C = D, D<E
A222 = B<C, C<>D, D>E
etc etc etc

if I was to code this conditions in column F it would be something
like this
"
if(and(A1="A",B>C,C=D,D<E),"A111",if(and(A1="A",B<C,C<>D,D>E),"A222",if
if if if if if etc etc etc

This is why I will end up with over 90 "if" & "And" statement in one
cell. I don't even know if it is possible. As you can see in every if
statement, there is condition A1="A". I just didn't know how to lock
it or make it common to all other statements.

Thanks again!
.

Relevant Pages

  • Re: Help! Search Range Using Two Criterias & Returning Cell Contents.
    ... For Each CELL In Range ... 'u can change them with your criterias address ... what I'm after is locating a employee based on his/her ... copy their employee particulars to a named range on the worksheet. ...
    (microsoft.public.excel.programming)
  • Help using the Lookup Function
    ... I need to search for a value based on two different criterias. ... I have only used VLookup or HLookup for 1 criteria, ... In Cell A1 there is a Validation list consisting of 5 choices. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Help using the Lookup Function
    ... Frank Kabel ... > I need to search for a value based on two different criterias. ... > In Cell A1 there is a Validation list consisting of 5 choices. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Comma Woes
    ... hmm actually when I open up a new excel sheet and in C1 I put in 4,6 and ... past that formula in cell D1 it returns #!VALUE! ... Prev by Date: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Torture and American television
    ... Matt Ion wrote: ... "The Cell" and had to shut them both down after about 10 minutes. ... Hmm, ... I thought the story could've been better--also, casting J-Lo in it didn't help either. ...
    (rec.arts.sf.tv.babylon5.moderated)