Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Numbers to $$
I need some help if you please
I have a COLUMN of arbitrary numbers & Blanks: A1=643, A2=10, A3 -A7=Blank, A8=1038. A9=1039,A10=9 etc,. I need a formula for (COLUMN B) that will convert COLUMN A into the Number 1 for the largest number (above B9=1), to the smallest number (A10=5), B3=BLANK, etc. In addition I need to corolate COLUMN C such that "B9" automatically inserts a largest pre-assigned doller value, "B8" is assigned a smaller pre-assigned doller value, "B1" gets a next smaller pre-assigned doller value, "A1" get a smaller & fixed 3rd doller value. All other rowes in COLUMN C are to remain blank. As far as the ROW 's go I have a fixed number of 20-- (Example: ROW 13-33, or 6-26, etc.). The rows can start at any ROW number. As far as daily entry's go I will be enterinting in groups of 5, 10, 15, or 20 enteries only. If this is to much to figure out just let me know as I do not wish to cause a ton of work. At any rate thank you for at least looking at it. fredmr. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Numbers to $$
Part 1:
In B1 you can use =RANK(A1,A:A) or =RANK(A1, $A$1:$A$20) with 20 replaced by the correct value. However the blanks with return #N/A so we could use =IF(ISNUMBER(A1),RANK(A1,A:A),"") Part 2: I do not follow best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "fredmr" wrote in message ... I need some help if you please I have a COLUMN of arbitrary numbers & Blanks: A1=643, A2=10, A3 -A7=Blank, A8=1038. A9=1039,A10=9 etc,. I need a formula for (COLUMN B) that will convert COLUMN A into the Number 1 for the largest number (above B9=1), to the smallest number (A10=5), B3=BLANK, etc. In addition I need to corolate COLUMN C such that "B9" automatically inserts a largest pre-assigned doller value, "B8" is assigned a smaller pre-assigned doller value, "B1" gets a next smaller pre-assigned doller value, "A1" get a smaller & fixed 3rd doller value. All other rowes in COLUMN C are to remain blank. As far as the ROW 's go I have a fixed number of 20-- (Example: ROW 13-33, or 6-26, etc.). The rows can start at any ROW number. As far as daily entry's go I will be enterinting in groups of 5, 10, 15, or 20 enteries only. If this is to much to figure out just let me know as I do not wish to cause a ton of work. At any rate thank you for at least looking at it. fredmr. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Numbers to $$
"Bernard Liengme" wrote: Part 1: In B1 you can use =RANK(A1,A:A) or =RANK(A1, $A$1:$A$20) with 20 replaced by the correct value. However the blanks with return #N/A so we could use =IF(ISNUMBER(A1),RANK(A1,A:A),"") Part 2: I do not follow best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "fredmr" wrote in message ... I need some help if you please I have a COLUMN of arbitrary numbers & Blanks: A1=643, A2=10, A3 -A7=Blank, A8=1038. A9=1039,A10=9 etc,. I need a formula for (COLUMN B) that will convert COLUMN A into the Number 1 for the largest number (above B9=1), to the smallest number (A10=5), B3=BLANK, etc. In addition I need to corolate COLUMN C such that "B9" automatically inserts a largest pre-assigned doller value, "B8" is assigned a smaller pre-assigned doller value, "B1" gets a next smaller pre-assigned doller value, "A1" get a smaller & fixed 3rd doller value. All other rowes in COLUMN C are to remain blank. As far as the ROW 's go I have a fixed number of 20-- (Example: ROW 13-33, or 6-26, etc.). The rows can start at any ROW number. As far as daily entry's go I will be enterinting in groups of 5, 10, 15, or 20 enteries only. If this is to much to figure out just let me know as I do not wish to cause a ton of work. At any rate thank you for at least looking at it. fredmr. thanks bernard, i'll see what i can do with this. i will let you know. (see-no caps) fredmr |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Numbers to $$
"Bernard Liengme" wrote: Part 1: In B1 you can use =RANK(A1,A:A) or =RANK(A1, $A$1:$A$20) with 20 replaced by the correct value. However the blanks with return #N/A so we could use =IF(ISNUMBER(A1),RANK(A1,A:A),"") Part 2: I do not follow best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "fredmr" wrote in message ... I need some help if you please I have a COLUMN of arbitrary numbers & Blanks: A1=643, A2=10, A3 -A7=Blank, A8=1038. A9=1039,A10=9 etc,. I need a formula for (COLUMN B) that will convert COLUMN A into the Number 1 for the largest number (above B9=1), to the smallest number (A10=5), B3=BLANK, etc. In addition I need to corolate COLUMN C such that "B9" automatically inserts a largest pre-assigned doller value, "B8" is assigned a smaller pre-assigned doller value, "B1" gets a next smaller pre-assigned doller value, "A1" get a smaller & fixed 3rd doller value. All other rowes in COLUMN C are to remain blank. As far as the ROW 's go I have a fixed number of 20-- (Example: ROW 13-33, or 6-26, etc.). The rows can start at any ROW number. As far as daily entry's go I will be enterinting in groups of 5, 10, 15, or 20 enteries only. If this is to much to figure out just let me know as I do not wish to cause a ton of work. At any rate thank you for at least looking at it. fredmr. Bernard, The first part worked like a charm. As far as the rest of my problem I will take that up separately. Thanks again. fredmr |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting hr min sec into numbers | Excel Worksheet Functions | |||
Converting positive numbers to negative numbers | Excel Worksheet Functions | |||
Converting numbers formatted as text to numbers | Excel Discussion (Misc queries) | |||
Converting Numbers | Excel Discussion (Misc queries) | |||
converting to numbers | Excel Worksheet Functions |