Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for a comparison
Hello,
I would like the code for the following: Compare the numbers in column I to column J (column I will always be a bigger number). Display the result in column K in a percentage off high (meaning column I) Its ok to display like: -7, no need to put the % sign. example 49.35 is column I, 47 is column J column K should read -5, meaning 47 is 5% away from 49.35 and just display it as -5 Thanks in advance -- Mike B |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for a comparison
You can do this easily just with standard calculations in column K; e.g. (for
K1): = (I1-J1)/J1 (formatted properly, would give %) = 100*(I1-J1)/J1 gives the number in the format you mention "mike b" wrote: Hello, I would like the code for the following: Compare the numbers in column I to column J (column I will always be a bigger number). Display the result in column K in a percentage off high (meaning column I) Its ok to display like: -7, no need to put the % sign. example 49.35 is column I, 47 is column J column K should read -5, meaning 47 is 5% away from 49.35 and just display it as -5 Thanks in advance -- Mike B |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for a comparison
How can I get this to go thru the 800+ lines of data? asutomatically?
I want it to do this with a macro and the amount of lines of data changes, the data can be large so I dont want it limited until there is no more data Thanks Mike "K Dales" wrote: You can do this easily just with standard calculations in column K; e.g. (for K1): = (I1-J1)/J1 (formatted properly, would give %) = 100*(I1-J1)/J1 gives the number in the format you mention "mike b" wrote: Hello, I would like the code for the following: Compare the numbers in column I to column J (column I will always be a bigger number). Display the result in column K in a percentage off high (meaning column I) Its ok to display like: -7, no need to put the % sign. example 49.35 is column I, 47 is column J column K should read -5, meaning 47 is 5% away from 49.35 and just display it as -5 Thanks in advance -- Mike B |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for a comparison
You could just enter the formula as given and drag it down to the botton of
your data but if you REALLY want a macro to do it then try: Sub InsCalc() Dim endRow As Long endRow = Cells(Rows.Count, 9).End(xlUp).row With Range(Cells(2, 11), Cells(endRow, 11)) .FormulaR1C1 = "=-100*(RC[-2]-RC[-1])/RC[-1]" .NumberFormat = "0" End With End Sub Regards Rowan "mike b" wrote: How can I get this to go thru the 800+ lines of data? asutomatically? I want it to do this with a macro and the amount of lines of data changes, the data can be large so I dont want it limited until there is no more data Thanks Mike "K Dales" wrote: You can do this easily just with standard calculations in column K; e.g. (for K1): = (I1-J1)/J1 (formatted properly, would give %) = 100*(I1-J1)/J1 gives the number in the format you mention "mike b" wrote: Hello, I would like the code for the following: Compare the numbers in column I to column J (column I will always be a bigger number). Display the result in column K in a percentage off high (meaning column I) Its ok to display like: -7, no need to put the % sign. example 49.35 is column I, 47 is column J column K should read -5, meaning 47 is 5% away from 49.35 and just display it as -5 Thanks in advance -- Mike B |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
row comparison | Excel Worksheet Functions | |||
Price Comparison Macro - This Is For An Excel MVP | Excel Discussion (Misc queries) | |||
Comparison | Excel Worksheet Functions | |||
HELP 2sht comparison MACRO | Excel Discussion (Misc queries) | |||
Comparison | Excel Programming |