If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
What Formula to use?
Hello everyone, I'm trying to figure out how to have excell show me a number
based on the value of another cell. Here is the cell info: 1 1.0 0.999 0.99900 MAX 1.00067 2 2.0 2.000 1.00000 MIN 0.99900 3 3.0 3.002 1.00067 4 4.0 4.000 1.00000 5 5.0 5.002 1.00040 6 6.0 6.004 1.00067 7 7.0 7.001 1.00014 8 8.0 8.000 1.00000 9 9.0 8.998 0.99978 I need to show that the Min and Max values is equal to the value in the first colum. However the valus if identical woul alwys go to the highest number. So Min is equal to 1 and then Max would default to 6 instead of 3 . Any help would be great.Thanks...Paul |
#2
|
|||
|
|||
What Formula to use?
Unclear! What is the meaning of THEN in "So Min is equal to 1 and then Max
would default to 6 instead of 3" If you just want to know that the largest A value for the Max in D, then I think we need to use VBA Is this what is needed? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Paul Chung" wrote in message ... Hello everyone, I'm trying to figure out how to have excell show me a number based on the value of another cell. Here is the cell info: 1 1.0 0.999 0.99900 MAX 1.00067 2 2.0 2.000 1.00000 MIN 0.99900 3 3.0 3.002 1.00067 4 4.0 4.000 1.00000 5 5.0 5.002 1.00040 6 6.0 6.004 1.00067 7 7.0 7.001 1.00014 8 8.0 8.000 1.00000 9 9.0 8.998 0.99978 I need to show that the Min and Max values is equal to the value in the first colum. However the valus if identical woul alwys go to the highest number. So Min is equal to 1 and then Max would default to 6 instead of 3 . Any help would be great.Thanks...Paul |
#3
|
|||
|
|||
What Formula to use?
Hi,
I'm not sure how you came up with a result of 6 since the max is 6.004 based on the data you gave us, and the min is 0.999 not 1? However, based on the general idea: =MAX(A1:A9*(MAX(B1:B9)=B1:B9)) =MAX(A1:A9*(MIN(B1:B9)=B1:B9)) Both of these formulas are Array Entered - that means you press Shift+Ctrl+Enter to enter them not Enter. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paul Chung" wrote: Hello everyone, I'm trying to figure out how to have excell show me a number based on the value of another cell. Here is the cell info: 1 1.0 0.999 0.99900 MAX 1.00067 2 2.0 2.000 1.00000 MIN 0.99900 3 3.0 3.002 1.00067 4 4.0 4.000 1.00000 5 5.0 5.002 1.00040 6 6.0 6.004 1.00067 7 7.0 7.001 1.00014 8 8.0 8.000 1.00000 9 9.0 8.998 0.99978 I need to show that the Min and Max values is equal to the value in the first colum. However the valus if identical woul alwys go to the highest number. So Min is equal to 1 and then Max would default to 6 instead of 3 . Any help would be great.Thanks...Paul |
#4
|
|||
|
|||
What Formula to use?
Cell C divided by B gives value D. I used the Min And Max for A1 through
A9. Now I need a Cell to convert the given number to a value from Colum A as 1 through 9. Sorry if I didn't pass this on......Paul "Shane Devenshire" wrote in message ... Hi, I'm not sure how you came up with a result of 6 since the max is 6.004 based on the data you gave us, and the min is 0.999 not 1? However, based on the general idea: =MAX(A1:A9*(MAX(B1:B9)=B1:B9)) =MAX(A1:A9*(MIN(B1:B9)=B1:B9)) Both of these formulas are Array Entered - that means you press Shift+Ctrl+Enter to enter them not Enter. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paul Chung" wrote: Hello everyone, I'm trying to figure out how to have excell show me a number based on the value of another cell. Here is the cell info: A B C D 1 1.0 0.999 0.99900 MAX 1.00067 2 2.0 2.000 1.00000 MIN 0.99900 3 3.0 3.002 1.00067 4 4.0 4.000 1.00000 5 5.0 5.002 1.00040 6 6.0 6.004 1.00067 7 7.0 7.001 1.00014 8 8.0 8.000 1.00000 9 9.0 8.998 0.99978 I need to show that the Min and Max values is equal to the value in the first colum. However the valus if identical woul alwys go to the highest number. So Min is equal to 1 and then Max would default to 6 instead of 3 . Any help would be great.Thanks...Paul |
#5
|
|||
|
|||
What Formula to use?
MIN and MAX are the values from Cells D1-D9. I need to show the value that
is in Cells A1-A9. However note that the Max could be either 3 or 6 in the example so we nee a rule to make sure it goes to the highest such as 6 in the example....Paul "Bernard Liengme" wrote in message ... Unclear! What is the meaning of THEN in "So Min is equal to 1 and then Max would default to 6 instead of 3" If you just want to know that the largest A value for the Max in D, then I think we need to use VBA Is this what is needed? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Paul Chung" wrote in message ... Hello everyone, I'm trying to figure out how to have excell show me a number based on the value of another cell. Here is the cell info: 1 1.0 0.999 0.99900 MAX 1.00067 2 2.0 2.000 1.00000 MIN 0.99900 3 3.0 3.002 1.00067 4 4.0 4.000 1.00000 5 5.0 5.002 1.00040 6 6.0 6.004 1.00067 7 7.0 7.001 1.00014 8 8.0 8.000 1.00000 9 9.0 8.998 0.99978 I need to show that the Min and Max values is equal to the value in the first colum. However the valus if identical woul alwys go to the highest number. So Min is equal to 1 and then Max would default to 6 instead of 3 . Any help would be great.Thanks...Paul |
#6
|
|||
|
|||
What Formula to use?
Its not clear which data is in which column. It is not clear what formulas
are in columns A, B, C. The explanation below is still not clear. How did you use Min and Max in A1:A9? Please show us the formula(s). Clarity is the key to getting fast, accurate answers here at the newsgroup. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paul Chung" wrote: Cell C divided by B gives value D. I used the Min And Max for A1 through A9. Now I need a Cell to convert the given number to a value from Colum A as 1 through 9. Sorry if I didn't pass this on......Paul "Shane Devenshire" wrote in message ... Hi, I'm not sure how you came up with a result of 6 since the max is 6.004 based on the data you gave us, and the min is 0.999 not 1? However, based on the general idea: =MAX(A1:A9*(MAX(B1:B9)=B1:B9)) =MAX(A1:A9*(MIN(B1:B9)=B1:B9)) Both of these formulas are Array Entered - that means you press Shift+Ctrl+Enter to enter them not Enter. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paul Chung" wrote: Hello everyone, I'm trying to figure out how to have excell show me a number based on the value of another cell. Here is the cell info: A B C D 1 1.0 0.999 0.99900 MAX 1.00067 2 2.0 2.000 1.00000 MIN 0.99900 3 3.0 3.002 1.00067 4 4.0 4.000 1.00000 5 5.0 5.002 1.00040 6 6.0 6.004 1.00067 7 7.0 7.001 1.00014 8 8.0 8.000 1.00000 9 9.0 8.998 0.99978 I need to show that the Min and Max values is equal to the value in the first colum. However the valus if identical woul alwys go to the highest number. So Min is equal to 1 and then Max would default to 6 instead of 3 . Any help would be great.Thanks...Paul |
#7
|
|||
|
|||
What Formula to use?
Bob, You are correct. I'll try to make it clearer. What I'm trying to do is
show the value of another cell but run into two problems in trying to accomplish this. Let's start from the beginning. Column A is my Dial 0 through 9. Column B is my value assigned Nominal. Column C is the values I read when testing my switch. Column D is =SUM(C/B) values. First, I need to get my the MIN and MAX formula to show the values so I create F and use the formula's: =MAX(D715) and =MIN(D715). However, if the values are the same in Column D, I need to show the cell with the higher of the two numbers that's the first problem. The next problem would have me show the values in Column F as a number associated to my switch setting in Column A. So for example my MAX in F of 1.00067 could be switch setting 3 or 6 in column A. I want to show the higher number, and place that value in the cell next to it. I hope this gets my point across better and thanks again for any assistance you and Bernard can provide....Paul "Paul Chung" wrote in message ... Hello everyone, I'm trying to figure out how to have excell show me a number based on the value of another cell. Here is the cell info: 1 1.0 0.999 0.99900 MAX 1.00067 2 2.0 2.000 1.00000 MIN 0.99900 3 3.0 3.002 1.00067 4 4.0 4.000 1.00000 5 5.0 5.002 1.00040 6 6.0 6.004 1.00067 7 7.0 7.001 1.00014 8 8.0 8.000 1.00000 9 9.0 8.998 0.99978 I need to show that the Min and Max values is equal to the value in the first colum. However the valus if identical woul alwys go to the highest number. So Min is equal to 1 and then Max would default to 6 instead of 3 . Any help would be great.Thanks...Paul |
Thread Tools | |
Display Modes | |
|
|