Question description:
This user has given permission to use the problem statement for this
blog.
I have created a new column in which I need to automatically show either "yes" or "no depending on whether the value of a previous column has a number less than or greater than 2000.
Solved by G. L. in 28 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
23/05/2018 - 05:20
Hello
User
23/05/2018 - 05:20
Hi!
Excelchat Expert
23/05/2018 - 05:20
Hi
Excelchat Expert
23/05/2018 - 05:21
How are you ?
User
23/05/2018 - 05:21
Good thanks
Excelchat Expert
23/05/2018 - 05:21
So you need to insert Yes or no at colulmn B
User
23/05/2018 - 05:21
Yes
User
23/05/2018 - 05:21
I need it to be automatic
Excelchat Expert
23/05/2018 - 05:21
OK
Excelchat Expert
23/05/2018 - 05:21
Depend on the value 2000
Excelchat Expert
23/05/2018 - 05:22
so if the value greater than or equal 2000 then insert Yes ?
User
23/05/2018 - 05:22
Yes exactly
Excelchat Expert
23/05/2018 - 05:23
Use the if statement
User
23/05/2018 - 05:23
I need to do all of the following actually
User
23/05/2018 - 05:23
Create another column that auto populates to say “yes” or “no” if the contract value is predicted to be worth $2000 and above. Call it Over $24,000 ARR.
Sum the predicted contract values for the total number of outstanding contracts
Create a formula to show of all the outstanding deals, which percentage belongs to DW, AR, and BK
Remove the gridlines
Add a filter and sort by descending start date
User
23/05/2018 - 05:24
Sorry I didn't see how you did that!
Excelchat Expert
23/05/2018 - 05:24
Ok
Excelchat Expert
23/05/2018 - 05:25
Insert the formula =IF(A2>=2000,"Yes","No")
Excelchat Expert
23/05/2018 - 05:25
at the cell B2 and drag if for all the rows
Excelchat Expert
23/05/2018 - 05:26
What is the ARR?
User
23/05/2018 - 05:27
I think maybe annual revenue requirement
User
23/05/2018 - 05:27
How do I insert a formula
Excelchat Expert
23/05/2018 - 05:27
copy and paste it
Excelchat Expert
23/05/2018 - 05:27
Or type it at cell B2
Excelchat Expert
23/05/2018 - 05:28
You want to insert it at your file ?
User
23/05/2018 - 05:29
Yes. So the actual column on my file is C2. So would it be =IF(C2>=2000,"Yes","No")
Excelchat Expert
23/05/2018 - 05:29
The data is at column C
Excelchat Expert
23/05/2018 - 05:30
Yes have to refer the formula to the cell that has data.
Excelchat Expert
23/05/2018 - 05:30
For example if the data is at column D like the preview .....
Excelchat Expert
23/05/2018 - 05:31
then the formula =IF(D2>=2000,"Yes","No")
Excelchat Expert
23/05/2018 - 05:32
The condition of the formula is if A2 greater or equal 2000 then return Yes
Excelchat Expert
23/05/2018 - 05:33
And No for other values.
Excelchat Expert
23/05/2018 - 05:33
Got it !!
User
23/05/2018 - 05:33
Yes Thanks!
User
23/05/2018 - 05:33
What about the second step?
User
23/05/2018 - 05:34
Sum the predicted contract values for the total number of outstanding contracts
Excelchat Expert
23/05/2018 - 05:34
What is the predicted contract values
Excelchat Expert
23/05/2018 - 05:35
Is it the Yes values ?
User
23/05/2018 - 05:35
The numbers in the contract value column I think?
Excelchat Expert
23/05/2018 - 05:36
What is the outstanding contracts??
Excelchat Expert
23/05/2018 - 05:37
for summing of the contract value, use the sum formula.
Excelchat Expert
23/05/2018 - 05:37
Like this
Excelchat Expert
23/05/2018 - 05:38
=SUM(A2:A7)
Excelchat Expert
23/05/2018 - 05:38
Which sum all the values at the range form A2 to A7
User
23/05/2018 - 05:38
where do I paste that formula?
Excelchat Expert
23/05/2018 - 05:45
Any cell
Excelchat Expert
23/05/2018 - 05:46
Where it needed
User
23/05/2018 - 05:46
Create a formula to show of all the outstanding deals, which percentage belongs to DW, AR, and BK
User
23/05/2018 - 05:46
This is the next question
User
23/05/2018 - 05:46
And the DW AR and BK cells are...
Excelchat Expert
23/05/2018 - 05:49
What is this cells mean
Excelchat Expert
23/05/2018 - 05:49
and what is the outstanding deals?
User
23/05/2018 - 05:52
Can i erase the current document we have and show you the full thing?
User
23/05/2018 - 05:52
I've been copying and pasting as I go
User
23/05/2018 - 05:52
Oh wait i added a tab
User
23/05/2018 - 05:52
one sec
Excelchat Expert
23/05/2018 - 05:53
Ok
User
23/05/2018 - 05:56
K done
User
23/05/2018 - 05:56
So the question again is:
User
23/05/2018 - 05:56
Create a formula to show of all the outstanding deals, which percentage belongs to DW, AR, and BK
Excelchat Expert
23/05/2018 - 05:59
Is this all the column at your sheet ?
User
23/05/2018 - 05:59
Yes
Excelchat Expert
23/05/2018 - 06:00
What do you mean by the outstanding deala
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
