I have a question about a formula for my data analysis spreadsheet. I would like to establish some rules in the result column of my spreadsheet. The rules are if the final DO (column C) is <1 a > symbol is placed in front of the result(column G). If the depletion (column D) is <2 a < symbol is placed in front of the result. And if the numbers pass both rules then a number without any signs is produced. What formula would I need to do this? Thanks
Last edited by buck08; 07-20-2017 at 11:21 AM.
Also please check your thread description as it does mot match your file.
The file attach is just an example of what I'm working on. The rules in the file I've sent are ones that pass, but on occasion I have samples that don't pass the rules, so I need to create a complex formula in the result column to account for those times.
Just checking re symbols in statement aboveis < 1 a > symbol is placed in front of the result(column G). If the depletion (column D) is < 2 a < symbol
If C >1 AND D>2 then no symbol? If C > 1 OR D > 2 add sysmbol?
Something like this ...
=IF(AND(C2 >1,D2>2),ROUND((F2*300)/(A2),0),IF(OR(C2 >1,D2 >2)," > "&ROUND((F2*300)/(A2),0)))
Yes, in my result column I need a < sign on my result if the depletion is <2, a > sign if my Final DO is <1 and no symbol if the results follow the rules in red on my sheet
TRY
=IF(AND(C2>1,D2>2),ROUND((F2*300)/(A2),0),IF(D2<2,"< "&ROUND((F2*300)/(A2),0),IF(C2<1,"> "&ROUND((F2*300)/(A2),0))))
You may need to change the AND(......)
Nevermind, I didn't type the formula in right.
Thanks for the help!! I've been trying to get some actual help with this for about a week now.
How do I account for those <> symbols in the average cell? I want the symbols to show up in the average cell if one or both of the results has a symbol attached. Right now it has the #value! in the cell.
It says I've entered too many arguments for this function
If I understand then the following modification of John's formula will display "<>" if final D.O. is less than 1 and depletion is less than 2:Formula:
=IF(AND(C2>1,D2>2),ROUND((F2*300)/(A2),0),IF(AND(C2<1,D2<2),"<>"&ROUND((F2*300)/(A2),0),IF(D2<2,"< "&ROUND((F2*300)/(A2),0),IF(C2<1,"> "&ROUND((F2*300)/(A2),0)))))
If I have misunderstood, please upload a sample of the file where the #value error is being displayed along with what you want the cell to display in this situation.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
John helped out a lot and that is all currently working. I've now moved on to the average cell of my spreadsheet and wanted help there. I have attached a new file to the thread. Thanks!
So this is a new query about averages - yes? If so, please mark this thread as solved and start a new one about the new query with the relevant file attached. Thanks!
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks