Question description:
This user has given permission to use the problem statement for this
blog.
Need assistance with if / or statement where result is calculating backwards
Solved by S. U. in 39 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
24/09/2017 - 03:23
Welcome to Go it Pro-Excel
Excelchat Expert
24/09/2017 - 03:23
How may i help you?
User
24/09/2017 - 03:24
have a few conditional formulas that i cannot seem to make work and i think that it is because they are all based off other calculated cells?
Excelchat Expert
24/09/2017 - 03:24
OK, can you show me how you are trying to do it?
User
24/09/2017 - 03:24
can see what i just pasted
Excelchat Expert
24/09/2017 - 03:25
Column P is the formula?
User
24/09/2017 - 03:25
lots of formulas - can i only get help on one
Excelchat Expert
24/09/2017 - 03:26
Ok i can not see the formula in this document
User
24/09/2017 - 03:26
i need the minimum of F, I, L - unless it is 0
User
24/09/2017 - 03:27
I want it to show the number bigger
Excelchat Expert
24/09/2017 - 03:27
Ok
User
24/09/2017 - 03:27
so in this example It would be 296.43
Excelchat Expert
24/09/2017 - 03:27
why you don't use the Max function
Excelchat Expert
24/09/2017 - 03:27
Instead of If
User
24/09/2017 - 03:27
because it could be that there would be a larger number there as well
Excelchat Expert
24/09/2017 - 03:28
=max(F2,I2,L2)
User
24/09/2017 - 03:28
i would not want 5000
User
24/09/2017 - 03:28
I would still want 296.43
User
24/09/2017 - 03:28
not 0
Excelchat Expert
24/09/2017 - 03:29
Ok
Excelchat Expert
24/09/2017 - 03:30
OK you want the minimum different than 0
User
24/09/2017 - 03:30
yes
Excelchat Expert
24/09/2017 - 03:30
And the maximum from where?
User
24/09/2017 - 03:31
no maix
Excelchat Expert
24/09/2017 - 03:31
OK
Excelchat Expert
24/09/2017 - 03:31
Do you want to see what is wrong with your formula or do you want me to create a new one?
User
24/09/2017 - 03:31
just fix it
User
24/09/2017 - 03:32
in N
Excelchat Expert
24/09/2017 - 03:32
ok, but it is not formula in N, just a number
User
24/09/2017 - 03:32
I know -
User
24/09/2017 - 03:33
that is where it goes tho
Excelchat Expert
24/09/2017 - 03:33
ok
Excelchat Expert
24/09/2017 - 03:33
got it
Excelchat Expert
24/09/2017 - 03:34
Min between quote #1, #2 and #3
Excelchat Expert
24/09/2017 - 03:34
right?
Excelchat Expert
24/09/2017 - 03:34
different than 0
User
24/09/2017 - 03:34
yess
Excelchat Expert
24/09/2017 - 03:35
OK
Excelchat Expert
24/09/2017 - 03:35
Im working with you
User
24/09/2017 - 03:35
Thanks - was a typo
Excelchat Expert
24/09/2017 - 03:37
Ok I got i quick solution
User
24/09/2017 - 03:37
Great
Excelchat Expert
24/09/2017 - 03:37
but is not supported in excel online
User
24/09/2017 - 03:37
hummm
Excelchat Expert
24/09/2017 - 03:37
Is an array formula
Excelchat Expert
24/09/2017 - 03:37
the other one is with If Function
Excelchat Expert
24/09/2017 - 03:38
im going to give you both
User
24/09/2017 - 03:38
I tried If - didnt work
User
24/09/2017 - 03:38
thanks
Excelchat Expert
24/09/2017 - 03:38
I gonna give you If over here
User
24/09/2017 - 03:38
ok
Excelchat Expert
24/09/2017 - 03:38
and you can try the other way in excel
User
24/09/2017 - 03:38
ok
Excelchat Expert
24/09/2017 - 03:43
Im working
Excelchat Expert
24/09/2017 - 03:43
let me fix something
User
24/09/2017 - 03:44
ok
Excelchat Expert
24/09/2017 - 03:45
=IF(AND(E2<>0,E2<H2,E2<K2),E2,IF(AND(H2<>0,H2<K2),H2,IF(K2<>0,K2,H2)))
Excelchat Expert
24/09/2017 - 03:45
OK here is with the If Function
User
24/09/2017 - 03:46
seems to work in my spreadsheet
User
24/09/2017 - 03:46
Thanks
Excelchat Expert
24/09/2017 - 03:46
You are welcome!
Excelchat Expert
24/09/2017 - 03:47
Entering to explanation Phase
Excelchat Expert
24/09/2017 - 03:48
I compare the first value the others if it is different than 0 and less than the others so this is the number, if not it move to the second nnumber
Excelchat Expert
24/09/2017 - 03:49
and later to the Third
User
24/09/2017 - 03:49
I understand completely now that i see it. Can i ask about another one
Excelchat Expert
24/09/2017 - 03:50
You need to create another session, but go ahead. we have time
User
24/09/2017 - 03:51
thanks
User
24/09/2017 - 03:52
ok - so - in PURCHASE PP
User
24/09/2017 - 03:52
I need it to say "PURCHASE" if
User
24/09/2017 - 03:52
savings $$ amount is over $125 or Savings % is over 25%
User
24/09/2017 - 03:53
otherwise it should be blank
Excelchat Expert
24/09/2017 - 03:54
=IF(OR(O2>125,P2>25),"PURCHASE","")
Excelchat Expert
24/09/2017 - 03:54
that formula does not include 125 and 25%
Excelchat Expert
24/09/2017 - 03:54
=IF(OR(O2>125,P2>0.25),"PURCHASE","")
Excelchat Expert
24/09/2017 - 03:54
sorry i forgot the 0.25
User
24/09/2017 - 03:55
Yes - that is exactly what i have in my spreadsheet - however - it only says purchase if the numbers are negitive and my signs are not backward
User
24/09/2017 - 03:55
s
Excelchat Expert
24/09/2017 - 03:56
Maybe you have "<" sing instead of ">"
Excelchat Expert
24/09/2017 - 03:57
How can you have negative numbers in savings?
Excelchat Expert
24/09/2017 - 03:58
The formula works for numbers higher than 125 and negative numbers are not higher than that
Excelchat Expert
24/09/2017 - 03:58
Entering to Discussion Phase
User
24/09/2017 - 03:58
right - but if the lowest quote is higher than the original quoted amount it creates a negative number
Excelchat Expert
24/09/2017 - 03:58
OK, got it
Excelchat Expert
24/09/2017 - 03:59
it Make sense
User
24/09/2017 - 03:59
so is it possible it is doing this because it is figuring off of calculated fields
Excelchat Expert
24/09/2017 - 04:00
Ummm
Excelchat Expert
24/09/2017 - 04:01
I should know better what is Quote 1 and original to help you better
Excelchat Expert
24/09/2017 - 04:01
Session is about to end
User
24/09/2017 - 04:01
I know - thnaks for your help
Excelchat Expert
24/09/2017 - 04:01
It Was my pleasure!
Excelchat Expert
24/09/2017 - 04:01
Have a nice 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.
