Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel formula divide list of #s by N to get a number above 40
I have a list of numbers. I need to divide each of those numbers by the same
number ("N") that will give me a resulting list in which all of the numbers are at least 40 or above. Is there a formula that will find such a number? |
#2
|
|||
|
|||
Hi
=MAX(40,A1/N) Arvi Laanemets "JudyK" wrote in message ... I have a list of numbers. I need to divide each of those numbers by the same number ("N") that will give me a resulting list in which all of the numbers are at least 40 or above. Is there a formula that will find such a number? |
#3
|
|||
|
|||
I am assuming yor list is in A1:A100, and all of the numbers are greater than
0. If we take smallest value in that range and divide it by N, choosing N so that the result is = 40, then by definition all larger numbers, when divided by N, will give a result 40. In other words, you want to start with this equation MIN(A1:A100)/N = 40 and solve it for the value of N: N = MIN(A1:A100)/40 So you want to divide by MIN(A1:A100)/40, i.e. =A1/(MIN(A1:A20)/40) PS: This problem has no solution if the list contains both positive and negative numbers: a negative number would have to be divided by a negative number to get a result = +40, whereas positive numbers have to be divided by a positive number. On Tue, 8 Feb 2005 13:05:02 -0800, "JudyK" wrote: I have a list of numbers. I need to divide each of those numbers by the same number ("N") that will give me a resulting list in which all of the numbers are at least 40 or above. Is there a formula that will find such a number? |
#4
|
|||
|
|||
Hi, Arvi:
If I understand correctly, all numbers in the list are to be divided by N. She needs to determine the value of N. I don't think your formula will do that, will it? N is a variable in your equation. On Tue, 8 Feb 2005 23:23:20 +0200, "Arvi Laanemets" wrote: Hi =MAX(40,A1/N) Arvi Laanemets "JudyK" wrote in message ... I have a list of numbers. I need to divide each of those numbers by the same number ("N") that will give me a resulting list in which all of the numbers are at least 40 or above. Is there a formula that will find such a number? |
#5
|
|||
|
|||
Hi
OP can replace N with any value, or formula, or cell reference! Formulas can depend on data type, but never on their orign . :-) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Myrna Larson" wrote in message ... Hi, Arvi: If I understand correctly, all numbers in the list are to be divided by N. She needs to determine the value of N. I don't think your formula will do that, will it? N is a variable in your equation. On Tue, 8 Feb 2005 23:23:20 +0200, "Arvi Laanemets" wrote: Hi =MAX(40,A1/N) Arvi Laanemets "JudyK" wrote in message ... I have a list of numbers. I need to divide each of those numbers by the same number ("N") that will give me a resulting list in which all of the numbers are at least 40 or above. Is there a formula that will find such a number? |
#6
|
|||
|
|||
"Myrna Larson" wrote:
If I understand correctly, all numbers in the list are to be divided by N. She needs to determine the value of N. I don't think your formula will do that Arvi Laanemets wrote: OP can replace N with any value, or formula, or cell reference! I would agree that Judy's question was very difficult to understand. But I think Myrna got it right. Judy wrote: "Is there a formula that will find such a number?" What number? Judy wrote: "I have a list of numbers. I need to divide each of those numbers by the same number ("N") that will give me a resulting list in which all of the numbers are at least 40 or above." Translation: Judy wants to find an N such that when each number in a list is divided by N, the result of the division will be no less than 40. Myrna's formula will do just that. It is very elegant and very obvious at the same time. Arvi's formula does force the result to be no less than 40. But that does not seem to be what Judy asked for. Of course, Judy might have simply misstated the problem she is trying to solve. |
#7
|
|||
|
|||
Hi
wrote in message oups.com... What number? Judy wrote: "I have a list of numbers. I need to divide each of those numbers by the same number ("N") that will give me a resulting list in which all of the numbers are at least 40 or above." Translation: Judy wants to find an N such that when each number in a list is divided by N, the result of the division will be no less than 40. My translation: Judy wants to create a new list of numbers, where every value is N times less than previous one, but not less than 40, i.e. when N=10 and old value was 800, then the new one will be 80, when old value was 400, the new value is 40, but when old value is p.e. 100, then the new value will be 40 again. Of-course OP has to copy mu formula to range (down the column). -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets |
#8
|
|||
|
|||
Yes, but the question is HOW TO CALCULATE N. What is the "value, or formula or
cell reference" supposed to be? On Wed, 9 Feb 2005 08:02:53 +0200, "Arvi Laanemets" wrote: Hi OP can replace N with any value, or formula, or cell reference! Formulas can depend on data type, but never on their orign . :-) |
#9
|
|||
|
|||
I think your translation is faulty.
She said "I need to divide each of those numbers by the same number". I don't see any ambiguity in that statement. N is a constant. Every number in the new list =OriginalNumber/N That's not what you are doing. You are dividing 800 by 10, and 400 by 10, but you are dividing 100 by 2.5. N is 10 in the 1st 2 cases, 2.5 in the 3rd. 10 < 2.5 That's not what she said she wanted. BTW, the formula I proposed for calculating N gives the SMALLEST possible value of N. Any number, x, where 0 < x <=N will also suffice. On Wed, 9 Feb 2005 12:54:49 +0200, "Arvi Laanemets" wrote: Hi wrote in message roups.com... What number? Judy wrote: "I have a list of numbers. I need to divide each of those numbers by the same number ("N") that will give me a resulting list in which all of the numbers are at least 40 or above." Translation: Judy wants to find an N such that when each number in a list is divided by N, the result of the division will be no less than 40. My translation: Judy wants to create a new list of numbers, where every value is N times less than previous one, but not less than 40, i.e. when N=10 and old value was 800, then the new one will be 80, when old value was 400, the new value is 40, but when old value is p.e. 100, then the new value will be 40 again. Of-course OP has to copy mu formula to range (down the column). |
#10
|
|||
|
|||
Hi
For OP, as follows from message, N is some constant, the value of which she didn't share with us Arvi Laanemets "Myrna Larson" wrote in message ... Yes, but the question is HOW TO CALCULATE N. What is the "value, or formula or cell reference" supposed to be? On Wed, 9 Feb 2005 08:02:53 +0200, "Arvi Laanemets" wrote: Hi OP can replace N with any value, or formula, or cell reference! Formulas can depend on data type, but never on their orign . :-) |
#11
|
|||
|
|||
Hi
"...that will give me a resulting list in which all of the numbers are at least 40 or above." So as I understood, she wants a list of different values. It isn't clear from OP's message, how much different values in list will be - but it isn't excluded, than for every original value a matching calculated value must be given. Exactly what my formula does, in my opinion. But really, the original message was somewhat messy, and allows different interpretations - depending on which part of it you are concentrated (and I'm afraid our 2 ones from this thread aren't all of them). I'm not saying your answer is wrong, but we can't be sure, which one (when any of them) is, what OP wanted, and there is no way to know it without her reporting back. Arvi Laanemets "Myrna Larson" wrote in message ... I think your translation is faulty. She said "I need to divide each of those numbers by the same number". I don't see any ambiguity in that statement. N is a constant. Every number in the new list =OriginalNumber/N That's not what you are doing. You are dividing 800 by 10, and 400 by 10, but you are dividing 100 by 2.5. N is 10 in the 1st 2 cases, 2.5 in the 3rd. No! I'm dividing all numbers with 10, but when the result is less than 40 (another constant), a constant 40 is returned instead of quotient 10 < 2.5 That's not what she said she wanted. BTW, the formula I proposed for calculating N gives the SMALLEST possible value of N. Any number, x, where 0 < x <=N will also suffice. On Wed, 9 Feb 2005 12:54:49 +0200, "Arvi Laanemets" wrote: Hi wrote in message roups.com... What number? Judy wrote: "I have a list of numbers. I need to divide each of those numbers by the same number ("N") that will give me a resulting list in which all of the numbers are at least 40 or above." Translation: Judy wants to find an N such that when each number in a list is divided by N, the result of the division will be no less than 40. My translation: Judy wants to create a new list of numbers, where every value is N times less than previous one, but not less than 40, i.e. when N=10 and old value was 800, then the new one will be 80, when old value was 400, the new value is 40, but when old value is p.e. 100, then the new value will be 40 again. Of-course OP has to copy mu formula to range (down the column). |
#12
|
|||
|
|||
NO! She asked us how to calculate the value of N.
N is some number. By definition, it is a number that, if you use it to divide each of a list of existing numbers, the result will be =40. I think you are misreading the OP's message. On Wed, 9 Feb 2005 21:42:36 +0200, "Arvi Laanemets" wrote: Hi For OP, as follows from message, N is some constant, the value of which she didn't share with us Arvi Laanemets "Myrna Larson" wrote in message .. . Yes, but the question is HOW TO CALCULATE N. What is the "value, or formula or cell reference" supposed to be? On Wed, 9 Feb 2005 08:02:53 +0200, "Arvi Laanemets" wrote: Hi OP can replace N with any value, or formula, or cell reference! Formulas can depend on data type, but never on their orign . :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003: Match one list against another and highlight differenc | Excel Worksheet Functions | |||
Need excel formula to display 28.50hrs in HRS & MINS? | Excel Worksheet Functions | |||
How do I pick a number from a list in Excel? | Excel Worksheet Functions | |||
Is there a formula to spell out a number in excel? | Excel Worksheet Functions | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions |