Hi All,
Please assist me.
I'm trying to find formula to extract the maximum width error in my report.
Due to my lack of knowledge in Excel formula, I only can try the MAX or MIN
value.
Thank you in advance :-)
Hi All,
Please assist me.
I'm trying to find formula to extract the maximum width error in my report.
Due to my lack of knowledge in Excel formula, I only can try the MAX or MIN
value.
Thank you in advance :-)
Let's take your first (incorrect) formula: =MAX(Data!B4:C5)
Here's that range:
Excel 2016 (Windows) 32 bit
B C 4 0 0 5 1 0
Sheet: Data
You say the result should be +2, so you are going to need to tell us why - where has that figure come from?
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.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi AliGW,
Sorry,it's my mistake at "1/10 revolution" , it supposed to be +1.
(I reattached the correct one)
At "1/2 revolution" the figure should be "-4" as the biggest dial gauge indicator error (from start point "0").
I can't find formula to count for biggest indication error ....
You can use SMALL and LARGE functions to check if their sum is greater or equal than zero.
When this happens, you want in the cell the positive value, otherwise the negative.
See the attached file for one possible solution.Please Login or Register to view this content.
Last edited by Hydraulics; 07-29-2018 at 05:02 AM. Reason: Added function
Aim high or don't even try.
---------------------------------
If your question has been answered, don't forget to mark the thread as SOLVED.
If you find an answer helpful, click on the star icon at the bottom of the post.
@Hydraulics
Sorry for the off-topic interjection:
Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
I'm sure you understand.
EDIT: Thank you for adding more detail -that's more helpful.
Last edited by AliGW; 07-29-2018 at 04:52 AM.
Your understanding of positive and negative numbers is flaky.
Consider your second attempt: =Data!B4-(MAX(Data!B4:C8))
Excel 2016 (Windows) 32 bit
B C 4 0 0 5 1 0 6 -2 -1 7 -4 -4 8 -4 -3
Sheet: Data
The maximum (i.e. largest) number in the defined array is 1 (B5): 0-1=-1.
So it's not the MAX you are looking for. Can you please define clearly what it is you ARE looking for? Is it the greatest variance from 0 every time?
If so, you will need to compare the MAX and the MIN in the range, and then return whichever is the greater difference from 0:
=IF(ABS(MAX(Data!B4:C8))>ABS(MIN(Data!B4:C8)),MAX(Data!B4:C8),MIN(Data!B4:C8))
Last edited by AliGW; 07-29-2018 at 04:16 AM.
Hi Ms AliGW,
I'm sorry about my explanation difficult to understand. I have a very bad English usage knowledge.
Actually you are correct, I'm looking for the greatest variance when compare to "0".
I have tried both solutions (from Hydraulic & you) & found both working the same.
Just one more question, in finding greatest variance (like in my case) which one
is better to use?
Thank you again for your assistance.
Regards
Use whichever solution you prefer - whichever you are more comfortable with and/or understand better so that you can adapt it in future.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks