**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.*