Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

IF(and(D<=250,QTY<=40000),600 , IF(and(D<=250,QTY>=40000),600*QTY/40000, IF(and(D>=250 , QTY>=40000) D*QTY/40000)))
Solved by O. B. in 18 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 24/04/2018 - 11:44
Hi
Excelchat Expert 24/04/2018 - 11:44
Welcome to Got it Pro
User 24/04/2018 - 11:44
Hello, thank you
Excelchat Expert 24/04/2018 - 11:45
Can you paste you sample data so that i can help you better
Excelchat Expert 24/04/2018 - 11:46
Are you there?
User 24/04/2018 - 11:46
sure one minute
Excelchat Expert 24/04/2018 - 11:46
ok
User 24/04/2018 - 11:46
I need to type it again in your spreadsheet. it will take me a minute is that ok?
Excelchat Expert 24/04/2018 - 11:46
ok
Excelchat Expert 24/04/2018 - 11:49
are you pasting?
User 24/04/2018 - 11:50
no just typed it but htat is pretty much it. Can you understand what I need?
User 24/04/2018 - 11:50
I need this to be simpler
Excelchat Expert 24/04/2018 - 11:50
That is the formula but that is not correct
Excelchat Expert 24/04/2018 - 11:50
can you paste your data on which this formula needs to be applied
User 24/04/2018 - 11:50
the formula is correct as far as the answe i need but it seem verey long.
User 24/04/2018 - 11:51
this is all I got.
Excelchat Expert 24/04/2018 - 11:51
yes it is correct now
Excelchat Expert 24/04/2018 - 11:51
why is it an issue if it s long
User 24/04/2018 - 11:52
Can this formula be made more simple or smaller
User 24/04/2018 - 11:52
Yes, that is correct it is too long
Excelchat Expert 24/04/2018 - 11:53
what exactly you want
Excelchat Expert 24/04/2018 - 11:53
tell me
Excelchat Expert 24/04/2018 - 11:53
i will build the formula
User 24/04/2018 - 11:54
if my distance is less than 250 miles and m y load is less than 40000, them is only 600
User 24/04/2018 - 11:55
IF my distance still less than 250 miles but my load is greater than 40000, then 600 * load/40000
User 24/04/2018 - 11:55
if my distance is greater than 250 miles and my l oad is greater than 40000, them what ever the distance is * load/40000
User 24/04/2018 - 11:56
Do you se e it?
Excelchat Expert 24/04/2018 - 11:56
Yes
Excelchat Expert 24/04/2018 - 11:56
that is exactly in your formula
User 24/04/2018 - 11:57
Yes but is too long. Can it be made simpler or shorter is the issue
User 24/04/2018 - 11:57
IF not them ????
Excelchat Expert 24/04/2018 - 11:57
If i would have written the formula i would have used it like you have done
User 24/04/2018 - 11:58
ok so that is as good as it gets I quess
Excelchat Expert 24/04/2018 - 11:58
what is A1>250
User 24/04/2018 - 11:58
A1 is the distance and 250 is miles
Excelchat Expert 24/04/2018 - 11:58
and b1<40000
User 24/04/2018 - 11:59
load less than 40000
Excelchat Expert 24/04/2018 - 11:59
what is A1>250 and B1<40000
Excelchat Expert 24/04/2018 - 11:59
what if?
Excelchat Expert 24/04/2018 - 11:59
i mean what if
User 24/04/2018 - 11:59
load is greater than 250 and load less than 40000
User 24/04/2018 - 11:59
what if? dont understand
Excelchat Expert 24/04/2018 - 11:59
see formula now
User 24/04/2018 - 11:59
The issues is can it be made simpler
Excelchat Expert 25/04/2018 - 12:00
i put 10000 is A1
Excelchat Expert 25/04/2018 - 12:00
earlier flase was coming
User 25/04/2018 - 12:00
still the same formula I put up. The point is can you make it smaller
Excelchat Expert 25/04/2018 - 12:00
false*
Excelchat Expert 25/04/2018 - 12:01
I cannot but it is the right formula and i would have also written it the same way
Excelchat Expert 25/04/2018 - 12:01
you have 3 if conditions with AND
User 25/04/2018 - 12:01
ok, thank you
Excelchat Expert 25/04/2018 - 12:01
So 6 times it would be written
User 25/04/2018 - 12:01
ok
Excelchat Expert 25/04/2018 - 12:01
That is what i am trying to tell
Excelchat Expert 25/04/2018 - 12:02
Please do give good rating
User 25/04/2018 - 12:02
thank you
Excelchat Expert 25/04/2018 - 12:02
i want to help you but this is the best way to write the formula
User 25/04/2018 - 12:02
ok
Excelchat Expert 25/04/2018 - 12:02
i hope you understand
User 25/04/2018 - 12:02
yes
Excelchat Expert 25/04/2018 - 12:02
It is a perfect formula
User 25/04/2018 - 12:02
thank you
Excelchat Expert 25/04/2018 - 12:02
Have a good 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.