**Question description:**

*This user has given permission to use the problem statement for this blog.*

Hi we need a formula that will take a vale see if its less than or greater than a set number and then apply another to a cell

Solved by D. J. in 40 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
12/06/2018 - 05:51

Hi there!

User
12/06/2018 - 05:52

hi

User
12/06/2018 - 05:52

ok this is my problem

Excelchat Expert
12/06/2018 - 05:52

Your required logic makes perfect sense. Might you be able to share the file you're working with so I can see exactly where the formula needs to go, what cells need to be included, etc.?

User
12/06/2018 - 05:54

ok see v1

Excelchat Expert
12/06/2018 - 05:54

Sure do

User
12/06/2018 - 05:54

i want it to be able to say if its less than

User
12/06/2018 - 05:55

or greater than 1500 i wat to add the vale of .4 to y1

User
12/06/2018 - 05:55

that easy enough

User
12/06/2018 - 05:56

but i need to do this for 1500 1600 1700 1800 1900 all the way up to 3000 and assign differant values to y

Excelchat Expert
12/06/2018 - 05:56

ok, we can make a nested IF statement.

Excelchat Expert
12/06/2018 - 05:57

So if it's LESS than 1500, put .4

Excelchat Expert
12/06/2018 - 05:57

So if it's LESS than 1600, put something else

Excelchat Expert
12/06/2018 - 05:57

If it's LESS than 1700, put something else

Excelchat Expert
12/06/2018 - 05:57

and so on?

User
12/06/2018 - 05:58

here is the what i have here

User
12/06/2018 - 05:58

b2

Excelchat Expert
12/06/2018 - 05:59

can you tell me all of the numbers that correspond to each amount

Excelchat Expert
12/06/2018 - 05:59

Can you tell me if this is correct:

Excelchat Expert
12/06/2018 - 05:59

LESS than 1500 = .40

Excelchat Expert
12/06/2018 - 05:59

LESS than 1600 .41

Excelchat Expert
12/06/2018 - 05:59

LESS than 1700 = .42

Excelchat Expert
12/06/2018 - 06:00

and so on.... Is that the logic that you need

Excelchat Expert
12/06/2018 - 06:04

Do you know what I mean? I just need to know the values for each increment as well as to confirm the logic and then I can code this for you.

User
12/06/2018 - 06:07

ok sorry so here is my list

User
12/06/2018 - 06:07

so anything under 1500 use .4

Excelchat Expert
12/06/2018 - 06:07

Ah very nice!

User
12/06/2018 - 06:07

and anything over 3000 use .55

Excelchat Expert
12/06/2018 - 06:08

Perfect

User
12/06/2018 - 06:08

your great

Excelchat Expert
12/06/2018 - 06:08

and the column/cell that is being checked isV1 correct?

Excelchat Expert
12/06/2018 - 06:08

and the result goes into column/cell Y1?

User
12/06/2018 - 06:08

the cells will difer per sheet

User
12/06/2018 - 06:09

differ

Excelchat Expert
12/06/2018 - 06:09

You can just copy the formula down for sure from Y1 down

Excelchat Expert
12/06/2018 - 06:09

ok. you will have to adjust the formula depending on what cell is being checked

Excelchat Expert
12/06/2018 - 06:09

but I can show you that.

Excelchat Expert
12/06/2018 - 06:09

I will code this into cell B2

Excelchat Expert
12/06/2018 - 06:09

Which will reference cell A2

User
12/06/2018 - 06:10

perfect

Excelchat Expert
12/06/2018 - 06:10

what if it lands on the number

Excelchat Expert
12/06/2018 - 06:10

like say it's 1600

Excelchat Expert
12/06/2018 - 06:11

is it .41 or .42

User
12/06/2018 - 06:11

1500-1600 should = .4

User
12/06/2018 - 06:11

im sorry

User
12/06/2018 - 06:11

1500-1599 =.4

Excelchat Expert
12/06/2018 - 06:12

perfect

User
12/06/2018 - 06:12

1600-1699 = .41

User
12/06/2018 - 06:12

and so on

Excelchat Expert
12/06/2018 - 06:12

Thanks so much

Excelchat Expert
12/06/2018 - 06:12

I will code this as fast as I can

User
12/06/2018 - 06:13

no worries

User
12/06/2018 - 06:13

im going to ste away for a sec

Excelchat Expert
12/06/2018 - 06:13

sure thing

User
12/06/2018 - 06:13

step

Excelchat Expert
12/06/2018 - 06:16

Ok friend please test cell A2 and see if cell B2 automatically calculates how you need it to.

Excelchat Expert
12/06/2018 - 06:16

So you would just take this formula:

Excelchat Expert
12/06/2018 - 06:16

=if(A2<1600,0.4,if(A2<1700,0.41,if(A2<1800,0.42,if(A2<1900,0.43,if(A2<2000,0.44,if(A2<2100,0.45,if(A2<2200,0.46,if(A2<2300,0.47,if(A2<2400,0.48,if(A2<2500,0.49,if(A2<2500,0.5,if(A2<2600,0.51,if(A2<2700,0.52,if(A2<2800,0.53,if(A2<2900,0.54,if(A2<3000,0.55,0.55))))))))))))))))

Excelchat Expert
12/06/2018 - 06:17

and then whatever cell is being evaluated you just change A2 to that cell

Excelchat Expert
12/06/2018 - 06:17

ie. Y2 etc.

Excelchat Expert
12/06/2018 - 06:17

Once the formula is in its proper column you can drag it down for sure.

Excelchat Expert
12/06/2018 - 06:18

Let me know if all is good once you review.

User
12/06/2018 - 06:18

let me try it please

Excelchat Expert
12/06/2018 - 06:18

Of course

User
12/06/2018 - 06:22

WORKED PRFECT WOW YOUR THE BEST

Excelchat Expert
12/06/2018 - 06:22

Oh that's amazing!

Excelchat Expert
12/06/2018 - 06:22

It's my pleasure to help!

Excelchat Expert
12/06/2018 - 06:22

Please don't hesitate to contact us again with a new session if you need further assistance - we will be happy to help.

User
12/06/2018 - 06:22

WHAT CAN I DO TO HELP YOU

Excelchat Expert
12/06/2018 - 06:23

haha well if you're happy with the session, you can end it at the top right corner and rate me according to how you felt!

Excelchat Expert
12/06/2018 - 06:23

Thanks so much and have the best day ~

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