• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I can't figure out this formula

Hello Everyone;
I am trying to format a cell to recognize statements:
=IF(ISBLANK(B2,C2),"NoData",IF(B2=M,C2>=54,"10",IF(B2=M,C2>=43,"9",IF(B2=M,C2>=23,"8",IF(B2=M,C2>=13,"7",IF(B2=M(,C2>=1,"6",)))))))
No luck so far:(
Any Suggestions?
 
Hi Raoul ,

I think there are too many changes needed to make your formula work the way you want it to ; it would be better if you can explain in plain English , what are the conditions you want tested , and what actions should happen if each test is TRUE , FALSE. This way , the required formula can be developed faster.

Narayan
 
there is a error is in your ... ISBLANK ... this can only check one cell you have references to two cells ... not sure if i can find anymore seeing as there is a error there but will have a look at the rest of the formula now
 
ok looked at the rest and i think what you are trying to do is check two criteria and if true put a number in a cell if False go check the next criteria
so you have if(B2=M,C2>54 ... I would guess this should be if(AND(B2=M,C2>54) ...

=IF(AND(B2<>"",C2<>""),IF(AND(B2="M",C2>=54),10,IF(AND(B2="M",C2>=43),9,IF(AND(B2="M",C2>=23),8,IF(AND(B2="m",C2>=13),7,IF(AND(B2="M",C2>=1),6,))))),"No Data")
This works for me might be what you need
forgot to say changed your isblank to IF(AND(B2<>"",C2<>""), in the above formula
 
Last edited:
Hi Rauol,

I will use a Lookup function, like the one shown in attached file, for such scenario.

@John()

Does => operator worked for you, because I got an error on it.

Regards,
 

Attachments

  • Formula_N.xlsx
    10.7 KB · Views: 2
Hi Rauol,

I will use a Lookup function, like the one shown in attached file, for such scenario.

@John()

Does => operator worked for you, because I got an error on it.

Regards,
Nice spot Somendra ... no => dosent work for me lol .... had formula posted and then i relailsed he needed = in it ... so edited and added = not realising it would give error in that spot .... re edited it to fix that
 
Hello Everyone,

I appreciate all the help. Actually this is what I need: I am trying to make a spreadsheet that will deceifer between a male and female score. I just listed the male requirements in the previous post. Cell B2 would be either M or F. C2 is the cell that the actual score would be documented. Here is the tricky part D2 would need to give a score based cell B2 and C2. I think D2 needs to be an OR statement. The numbers below would represent points per score.
Female
score/points
1-2= 6
3-12 = 7
13-32 = 8
33-43 = 9
44 plus = 10
Boys
score/points
1-12 =6
13 - 22 = 7
23-42 = 8
43-53 = 9
54 plus = 10

By the way, =IF(ISBLANK(C2),"No Data" worked great in a previous spreadsheet.
 
Hi Rauol,

I will use a Lookup function, like the one shown in attached file, for such scenario.

@John()

Does => operator worked for you, because I got an error on it.

Regards,
Hey John, The formula worked great! Is there a way to have the cell recognize the difference between a M and F and use a different set of numbers for F?
 
Hi Raoul ,

The ISBLANK function takes only one parameter , or a range specified using a start cell and an end cell.

Thus , you can have =ISBLANK(C2) or =ISBLANK(C2:D2) ; in the latter case , since the range is not a single cell , it will have to be entered as an array formula , using CTRL SHIFT ENTER. But you cannot have =ISBLANK(C2,D2)

However , even entering it as an array formula does not guarantee that it will return the logically correct results ; the proper way to enter it would be to use the OR or the AND function around it , and enter it as an array formula , using CTRL SHIFT ENTER.

Thus , you should only use it as follows :

=OR(ISBLANK(C2:D2))

=AND(ISBLANK(C2:D2))

Narayan
 
Hey Narayan;
This what I have so far.
=IF(OR(ISBLANK(B4),ISBLANK(D4)),"No Data",IF(B4="M",IFERROR(LOOKUP(D4,$S$1:$T$5),"Error in C2 Value")))

I would like to be able "M" or "F" in the B cell. I would like for the D cell to be able to recognize the difference and score it.
1 6 1 6
13 7 3 7
23 8 13 8
43 9 33 9
54 10 44 10
Boy Push Ups Girl Push-ups
 
Hi Raoul ,

It is not clear where your table is stored ; there are 4 columns shown , whereas in your formula you are using only columns S and T.

Narayan
 
Back
Top