Friday, October 5, 2012

If and else condition

Question:

Hi there,

My question is on conditional formatting based on values in a particular cell.
So let's say I make a form, and when users fill out the form, the answers will auto-populate in my spreadsheet.

Let's say:
Column A: "Incorrect 4th party calls"
Column B: "Incorrect cache-busting"

Can spreadsheets "know" that if cell A1 has phrase "Incorrect 4th party calls," it will assign a score in a new column (C)? For example, can we make it so that the existence of the phrase will assign a corresponding score in column C? In the same way, if cell B3 has the phrase "Incorrect cache-busting," can we make it so that the phrase can trigger a score in column D? Another option is whether spreadsheets can "know" that a value has been inputted into cell A1, and because there is a value, able to assign a score in C1?

Thanks,
Debbie



Solution:



Put the following formula in Cell C1 to check whether "Incorrect 4th party calls" exists or not:

=ArrayFormula(if(A:A="";"";(if(A:A="Incorrect 4th party calls";"entry exists";"entry doesn't exists"))))


OR put the following formula to calculate score in Cell C1, it will count no. of times "Incorrect 4th party calls" exists in Column A:

=Countif(A:A;"Incorrect 4th party calls")




I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,

2 comments:

  1. hey, thanks for your help!

    =ArrayFormula(if(D2="";"";(if(D2="4th Party Calls Missed/Incorrect";"0.25";"entry does not exist"))))

    i did this instead, so if the entry exists my cell will show 0.25. however, if my entry does not exist, it doesn't say "entry does not exist." is there an error there?

    thanks!

    ReplyDelete
    Replies
    1. It will display "entry does not exist." only when there is some entry in Column A.

      If column A is blank then it will also remain blank...

      try to put some text say "xyztext" in Column A, then you will get this message "entry does not exist".

      Delete