IF Statement Question

[Miss Sybil]

Hey All, i'm new to this forum and new to excel. It's a wonderful tool and i hope to learn more.
For example, I have a question on this formula. I want to use an IF statement if cell is =>65 then bring back 100 as a value.
If cell is <65 bring back whatever the cell value is, in this case 65. Is that possible?

In this instance if less than 65, how would I bring back the actually value? Is that possible with IF statement?
=if(A1>=65,100,0)

 

[Fluff]

Hi & welcome to MrExcel.
Just change the final 0 to A1

 

[Miss Sybil]

Thanks Fluff!

With that being said, could you check my formula below on the last bolded statement, BR19? I put the cell reference like you advised, but it brings back 0 instead of the actual value? What am I doing wrong? However, if the value is equal or greater than 70 it will populate "100"

=if(INDEX(ixl!$P$7:$CK$25,MATCH($A11,ixl!$A$7:$A$25,0),MATCH(BR4,ixl!$A$1:$CK$1,1))>=70,100,BR19)

 

[Fluff]

If it returns 0, that suggests that BR19 is either blank or 0
Should it return the cell found by the index formula?

 

[Miss Sybil]

If it returns 0, that suggests that BR19 is either blank or 0
Should it return the cell found by the index formula?

Yes, I'd like for it to return the value found by the index formula which wasn't a 0 or blank.

I'd like to achieve the results in column BS without having to create a new column for everything. I tried using: =if(INDEX(ixl!$P$7:$CK$25,MATCH($A17,ixl!$A$7:$A$25,0),MATCH(BS$4,ixl!$A$1:$CK$1,1))>=70,100,? )

What can I use so that the value will remain the same if it's a false argument? When I replaced the ? with the cell name it returned a 0. Is it not possible?

Thank you so much, Fluff!!!

 

[Fluff]

Do you have the LET function in you version of Excel?

 

[Fluff]

If you do have LET, you can use

=LET(Ind,INDEX(ixl!$P$7:$CK$25,MATCH($A17,ixl!$A$7:$A$25,0),MATCH(BS$4,ixl!$A$1:$CK$1,1)),IF(Ind>=70,100,Ind))

 

[Miss Sybil]

=LET(Ind,INDEX(ixl!$P$7:$CK$25,MATCH($A17,ixl!$A$7:$A$25,0),MATCH(BS$4,ixl!$A$1:$CK$1,1)),IF(Ind>=70,100,Ind))

Oooh! Thank you!!!! That seemed to work...but when I drag it so that it can be applied to the other cells, it returns the exact data. Where did I go wrong?

 

[Alex Blakenburg]

Oooh! Thank you!!!! That seemed to work...but when I drag it so that it can be applied to the other cells, it returns the exact data. Where did I go wrong?

Can you try and replace the ",1" in the second match formula to ",0" and let me know what happens.

=LET(Ind,INDEX(ixl!$P$7:$CK$25,MATCH($A17,ixl!$A$7:$A$25,0),MATCH(BS$4,ixl!$A$1:$CK$1,0)),IF(Ind>=70,100,Ind))

PS: Fluff is in the UK and is likely to be offline until tomorrow.

 

[Miss Sybil]

Can you try and replace the ",1" in the second match formula to ",0" and let me know what happens.

=LET(Ind,INDEX(ixl!$P$7:$CK$25,MATCH($A17,ixl!$A$7:$A$25,0),MATCH(BS$4,ixl!$A$1:$CK$1,0)),IF(Ind>=70,100,Ind))

PS: Fluff is in the UK and is likely to be offline until tomorrow.