Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc