Populate next Column to Right if blank

Topic

New Reply

This is ongoing from previous thread
http://windowssecrets.com/forums/showthread//158576-Multiple-If-formula-with-TEXT

I have provided a example workbook showing what the end process has to do with simulation.

Firstly, Module2 can be disregarded, it simply shows basically what’s going on, sort of like a web page’s real time data process_ simulator. But it illustrates what does happen, 2- 3 updates within the same minute, or 5M TOGO.

The problem is in Module1
Sub LOGGING
code is very wrong, it requires a complete overhaul.

The problem’s scenario is, there may be 2-3 updates within the same minute, the numericals A and B in Sheet 2 constantly change.
When there is a change, then
Sub TimerCalc2 activates. ( that section working OK. )

I need some sort of coding that populates the values in from Sheet2 Column B3:C28 to the next available 2 columns on the right IF those 2 columns on the right are blank.

The example show values up to row 11.
That’s Ok, it can be anywhere between Row 3 and 28

Thanks

Reply | Quote

Replies

I am confused about what you want exactly and what is “wrong” with the code. Does it give errors, is the logic wrong, where is it wrong, etc…

Could you provide a sample workbook of what you have at the start (before the “LOGGING” code is run) and what you want the results to be (using that example data) and walk us through (in words) what the code should be doing step by step.

I also don’t understand what your “simulation” is supposed to be representing. Could you elaborate?

Steve

Reply | Quote

Ok

I knew this problem of what I’m trying to ask will confuse, that is why I made the simulator.

The simulation is what will happen, each time there is an update or change in the A & B numbers from one chosen event. Let’s say we are tracking price changes or trends per minute via web query that is Looping consistently.

There can be 3 changes or 3 variations within 1 minute.

For every change or variation
Sub TimerCalc2() is activated,
so to place those A and B numbers in 2 columns and a graph will progress. ( graphing is under construction)

So at 20M is the first log, the web query Loops again and 15 seconds later the web query’s data indicates another change, yet again it may not, depending on what the trends are in real time.

Therefore
the first set of 20M numbers will be wiped out because they go into the same column
I need the new numbers A and B to go to the next column to the right.

This coding I wrote is wrong as in method.
It is static, 20M will always be in Range”D3:E27″ and so forth.
There are no errors in the code, it’s just the wrong method to apply to this situation.
This method is wrong, but I don’t know how to code a

Code:

If is not blank, then next 2 columns to the right

.
for the new set of A and B data.

This method causes more problems if I was to add more or less #M TOGO

Code:

Sub LOGGING()
Sheets(“Sheet1”).Select ‘OPTIONAL
Sheets(“Sheet1”).Range(“B3″).Select
””””””””””””””””””””””””””””
‘not the correct method is here.
If Sheets(“Sheet1”).Range(“B3”).Value = “20M TOGO” Then
   Sheets(“Sheet1”).Range(“D3:E27”).Value = Sheets(“Sheet1”).Range(“B3:C27”).Value
  End If

””””””””””””””””””””””””””””

If you look further down the simulators coding at;

‘
Sheets(“Sheet2”).Range(“C1”).Value = “5M TOGO”
TimerCalc2
‘
Application.Wait (Now + TimeValue(“0:00:02”)) ‘FOR TESTING
‘
Sheets(“Sheet2”).Range(“C1”).Value = “5M TOGO”
TimerCalc2

Notice I made it to Run “5M TOGO” twice. This is not a error.

It’s to show precisely the very point of error.
In real time trend updates,
2 sets of different A and B numbers will go into the same column twice, wiping out the first set.

The purpose of placing the A and B numbers in columns next to each other progressively is to eventually view a graph showing the rise or fall or trends in percent from -20 minutes before the start of an event to -0 minutes.

Hope this helps

Thanks

Reply | Quote

On Sheet2 Range C1, change to
0M TOGO
then activate the simulator, this will show more of the process.

Reply | Quote

Fixed for now,

I used a “marker” with Find so it goes to the next required Columns on Right.

A bit clunky, but not sure how else to code it.

Reply | Quote

Still not exactly sure what you want. Here is a complete guess, try it on a copy and see if this is what you are after. If not try to elaborate on the problem. Each time this runs it copys the timer column to A, the “A” list to the next avail column and the “B” column to the column after that. It labels the “A” and “B” that are added and puts in the value from C1.

Code:

Option Explicit
Sub NewLogging()
  Dim wSource As Worksheet
  Dim wDest As Worksheet
  Dim iColTimer As Integer
  Dim iColSourceA As Integer
  Dim iColSourceB As Integer
  Dim rCell As Range
  Dim lStartRow As Long
  Dim lLastRow As Long
  Dim iColDestA As Integer
  
  'Set as desired
  Set wSource = Worksheets("Sheet2")
  Set wDest = Worksheets("Sheet1")
  lStartRow = 4
  iColTimer = 2 'Column B
  iColSourceA = 6 'Column F
  iColSourceB = 11 'Column K
  Set rCell = wSource.Range("C1")
  
  iColDestA = wDest.Cells(lStartRow, wDest.Columns.Count).End(xlToLeft).Column + 1

  With wDest
    'add labels
    .Cells(lStartRow - 2, iColDestA) = "A"
    .Cells(lStartRow - 2, iColDestA + 1) = "B"
    .Cells(lStartRow - 1, iColDestA) = rCell.Value
  End With
  With wSource
    lLastRow = .Cells(.Rows.Count, iColSourceA).End(xlUp).Row
    'Copy Timer Info
    .Range(.Cells(lStartRow, iColTimer), .Cells(lLastRow, iColTimer)).Copy _
      wDest.Cells(lStartRow, 1)
    'Copy A
    .Range(.Cells(lStartRow, iColSourceA), .Cells(lLastRow, iColSourceA)).Copy _
      wDest.Cells(lStartRow, iColDestA)
    'copy B
    .Range(.Cells(lStartRow, iColSourceB), .Cells(lLastRow, iColSourceB)).Copy _
      wDest.Cells(lStartRow, iColDestA + 1)
  End With
End Sub

Steve

Reply | Quote

Thanks Steve,
your code works perfectly.

I implemented it in the simulator section, works fine.
A bit new for me to understand that type of coding, but I’m sure it makes sense.

XP

Reply | Quote

If you need an explanation of any the sections let me know (I recommend stepping through the code to see what it is doing)

Steve

Reply | Quote

It’s not the actual coding, it becomes obvious after the fact!
It’s the “mechanics” behind the coding.
For example, in my younger years, car had distributors and points, with a cam that opened and closed the points to make the spark. One can “see” it.
Today it’s all non movable parts enclosed in black boxes working the same in principle, via another method.

When a macro is recorded to do the basics then tweak it, it’s the same in principle.
The Dim’s and i’s and all that type to me is like that “black box”..

I m aware these codings are for some sort of memory pre-stuff
Option Explicit

Code:

Sub NewLogging()
  Dim wSource As Worksheet
  Dim wDest As Worksheet
  Dim iColTimer As Integer
  Dim iColSourceA As Integer
  Dim iColSourceB As Integer
  Dim rCell As Range
  Dim lStartRow As Long
  Dim lLastRow As Long
  Dim iColDestA As Integer

Tutorials and “books for dummies” sort of explain some, but the illustrations assume everyone works in finance,banks and has a degree in accounting.
If There were graphical line by line of what’s happening behind the actual coding, “behind the scenes” so to speak, and in simple layman’s, probably written by a layperson who once drove something as bold as a tractor.
That’s what my coding is like, a home made tractor !

Reply | Quote

The DIMS are only for Defining the variables that are being used (DIM is short for DIMensions). Defining the variables is not required, but it aids in many ways: it makes the code more readable and can help ensure that you don’t have any typos. I use the practice of having the first letter be based on the variable type and the name is descriptive (to aid in readability of the code).

The “graphical” nature (flowcharting) is still done, but it is not line-by-line. The code I do is small enough that I don’t flowchart it explicitly, but more envision the mechanics in my mind.

I have put some comments in the code to help understand, and as I mentioned, I will answer any specific questions. In general terms the code
Dimensions variables
Defines particular variables (sheets, cols, rows that will be used)
Calcs the next col on the dest sheet
On the dest sheet, puts A & B label and the value from C1
Then determines the last used row in Source “A”
Then copies the timer data to the first column
A to the next avail column
B to next avail column

Steve

Reply | Quote

Steve,
If I was to change anything, it be to add to A and B data.
Firstly,
The calculation are 2 types, and then secondly there is a graph, all of this during the same progression.

The first calculation/s is called “Overall Variance”. It means from the first A and B data column,
( Columns B an C) to the latest #M TOGO data.
It’s a percent of how much the Value has Decreased. So if the first was A 5.00 and the latest input became 2.50, then it will show -50% in red next to it.

So if at 20M TOGO was showing 5.00 and at the end of the progression to 1M TOGO it became 2.50,
the formula would be between the first and the last. Hence, progressively “Overall Variance”.
There is no need to graph that one.

The other progressive formula is at every update, same formula as the “Overall Variance”, but it’s for each new update.
So if it started at 20M then 2nd update at 17M and then15M, the Variance would be between the 17M and 15M.
The next update if at 13M, then the Variance will be between 15M and 13M, and so on.
It’s this progression that gets graphed.
—–,

The graph’s structure, the 3 boldest graph lines that go UP, are the one’s where the Values have Descended the most in percent.
So 5.50 ~ 2.50 is -50%, then the line would be going UP on the graph
If the next “lowest” is -45%, that red line on the graph would be going up to, same with the 3rd.
All others going up or down are blue lines.
Background is Black.

2 more questions
1.It may take me a few days,
should I get this DOS program fired up on another PC I have with DOSbox installed, manually type columns of numbers same as, do a screen capture to post here to show what the graph looks like ?
The graph section of that DOS program, the graph’s graphics don’t show up on XP platform.

2. Once the graphing is done, it’s basically the end of the project,
so I think I ought remain on this same thread?
Because I’d be using the same demo-workbook.

Reply | Quote

With all you have written, I am not exactly sure what you are asking (I don’t see any questions whatsoever before you mention “2 more questions”). If you are asking for help in this beginning part of your post, I think you need to reword and elaborate…

My take from it, is that you want some help with the charting. I see no problem with remaining on this thread. I would suggest that you post an example completed file (after you macro has some number of datapoints) since the example has all the same data for all the periods (so we can see changes in the charting).

You can post an example chart that is a “picture” (created in powerpoint of other graphic program) to give us a sense of what it should look like and what the axes are (based on the example data). If the dataset is not directly connected you may want to elaborate on what the X and Y data is explicitly so we understand.

Steve

Reply | Quote

I made some print-screen using the other PC with DOSBox.

Each screen shot is progressive to show a blank view of the program’s columns,
the first point of numerical data entry, ( manually typed in )

Each PAINT screen shot has a comment to explain what is going on. It shows the calculations, but I am not sure what the formula is, I know it’s Percents.

I think the graphics “speak” better than my explanations !

Hope the graphics are clear enough.

Thanks

Reply | Quote

I don’t follow what you are posting or what exactly you are asking for help with.

Please attach a file with sample excel data of what you start with and what you want with at the end if you want help with formulas or data manipulation. [The data does not have to be real, but it should be representative of what you will get]

If you want help creating a chart, please attach a sheet with the data you have that you want to chart and some representation of what the chart for that data should look like. The chart can be a screen shot, but you may have to explain what the X and Y values are in the data (especially if the chart data is not tabulated directly into the X/Y datasets

Steve

Reply | Quote

Please attach a file with sample excel data of what you start with and what you want with at the end if you want help with formulas or data manipulation. [The data does not have to be real, but it should be representative of what you will get]

Thanks,
I need to get the Formulas sorted first.

In cell’s value, N3 and O3 under the heading “DOS CALC” is (79.47) AND 35.00
This is what is showing in the DOS program/calculator’s calculations.

In Cells N4 and O4 I have used Formula as best as I know.
But am not getting the exact same Decimal points as the DOS calculator.

Once the formulas are sorted I’ll compare/improvise/test with the DOS calculator.

XP

Reply | Quote

XPD,

It looks like the original program converted the calculated percentage back to a regular number.
What you want depends on how it is used in the future and/or how you want it displayed.

If you just want the same answer you need to multiply the dividend by 100 ex: [noparse]=(J4-L$4)*100/J4[/noparse]
I’d also recommend a round function to control the number of decimal places to what you desire for example if you want 2 decimal places
[noparse]=Round((J4-L4)*100/J4,2)[/noparse]

The other option is if you just want to adjust the display you can format it as a percentage w/2 decimal places. If rounding this number you need to round it to 4 decimal places, ex: [noparse]=ROUND((L4-D4)/D4,4)[/noparse] formatted as:
35914-PctFmt
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG, the values to get the percent are in the uploaded wkbook.

D4: 4.00
L4: 5.40
DOS Calc shows 35.00
Formula in Excel I used is: =(L$4-$D$4)/$D$4
it shows: 0.35
A4 is the starting point, L4 is the current location in a progression.
Hence the, OVER-ALL, from A4 to wherever it’s at. The next web query update when there is a change
L4’s value will remain at 5.40 and a new value will be in N4, so the formula will automatically become
=(N$4-$D$4)/$D$4
This progression will be sorted out later on, first things first, the correct formula and format.
———-

The second calculation next to the first, is the current point
J4: 26.30
L4: 5.40
DOS Calc. shows: -79.47

“my” Formula in Excel is
=(J4-L$4)/J4
I get: 0.79

————–
I need to get the same as the DOS calculator, then I can have the correct Values for the graph.

Thanks.

Reply | Quote

Ok
I get the correct answer on:
Cell J4 Value: 26.30
Cell L4 Value: 5.40
Cell N4: Formula: =ROUND(($J4-$L4)*100/$J4,2)
79.47, correct.
———————-
I get a differant answer when:
Cell D4 Value: 4.00
Cell L4 value: 5.40
Cell O4 Formula: =ROUND(($L4-$D4)*100/$L4,2)
Answer in DOS Calc shows 35.00
Excel shows 25.93
——
?

Reply | Quote

The numbers look like percentages so the DOS program multiplied it by 100.

I suggest just formatting to percent, or if you want the percentage number use formulas like:
=(J4-L$4)/J4*100

and:
=(L$4-$D$4)/$D$4*100

Though this appears to be examining the change, it is calculated wrong. The correct way is:
=(Current-Previous)/Previous
or
=Current/Previous – 1

This gives a positive change (+) if the new value has increased in value and a negative (-) if the new value is lower. The values you have yield the opposite results.

Steve
In the sample I get in Excel:
=(L$4-$D$4)/$D$4
=0.3500 = 35.00%
or
=ROUND((L$4-$D$4)/$D$4*100,2)
=35.00

Reply | Quote

Thanks Steve,

The format of the percent at this stage is not important, it’s getting the right formula for the graph.

I have implemented those formula as suggested.

The way the graph works, the negatives are the lines that appear to move upwards, “In”
The Positives are down lines, “Out”.

XP

Reply | Quote

I am looking for that formula that does the Ascending or Descending function rather than a macro

Cell A3 No. ( text )
———————–
Range D4:D28 Value’s of A data, but not always filled up to Cell D28
Range L4:L28 NEW Value’s of A data, but not always filled up to Cell L28

Formula for Cell’s 04:O28
=($L$4-$D$4)/$D$4*100
=($L$5-$D$5)/$D$5*100
and so on….,
all good to this point.
——————————-,

Formula in question in Cell Range P4:P28
=INDEX($A4:$A28,MATCH(MIN($O4:$O28),$O4:$O28,0))

I keep getting the same Value from A4:A28

What I’m looking for,
( to avoid an Copy-Cut-Paste to another Worksheet-Ascend and then paste back, macro)
is that formula I think has offset or match but Ascends from the lowest.
Has to include, if Cell is blank in the O4:028 range OR has N/A, then show nothing in it’s respective cell.

It’s New Years Eve nearly here, so no hurry !

All the best for 2014.

Reply | Quote

Can you attach a workbook with the odd results. The last one only has values in row 4…

Your formula will return many of the same results dependent on where the min value in the range is. The range in the formula changes with each row down. It gets the value in A matching the min in current row to 25 rows down. The 25 cells it checks is always changing.

If it were “locked” it should always give the same result. What is the purpose of this calculation?
Steve

Reply | Quote

The purpose of the calculation is;
before the graph, to see the lowest numbers after comparisons, referenced from A4:A28 in Descending or Ascending order from the percent formula after every web query update.

The Ascending Formula is required in
Q4:Q28

Referencing O4:O28 to Ascend from
relating to A4:A28

In Cell O3 etc, Prv means Previous

In Cell R3, OVRL means Overall

XP

In the mean time, I’m coding a demo VBA, not finished,
but hope will help with explaining. It was from “Record Macro” then cleaned up.
A formula I hope can replace the Descendings etc

Code:

Sub Macro1()
Sheets(“Sheet4”).Select
Sheets(“Sheet4”).Range(“P4:Q26”).ClearContents
Sheets(“Sheet4”).Range(“S4:T26”).ClearContents
Sheets(“Sheet4”).Range(“V4:W26”).ClearContents
Sheets(“Sheet4”).Range(“Y4:Z26”).ClearContents
‘
Sheets(“Sheet1”).Range(“A1:B26″).ClearContents
””””””””””””””””””””””””
‘START REFERENCINGS AND ASCENDINGS
Sheets(“Sheet4”).Range(“A3”).Select
Sheets(“Sheet1”).Select ‘
Sheets(“Sheet1”).Range(“A1:A26”).Value = Sheets(“Sheet4”).Range(“A3:A28”).Value

    Sheets(“Sheet1”).Range(“B1:B26”).Value = Sheets(“Sheet4”).Range(“O3:O28”).Value
    Sheets(“Sheet1”).Select
    Sheets(“Sheet1”).Range(“B2”).Select
    ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“B2”), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(“Sheet1”).Sort
        .SetRange Range(“A2:B10”)
        .Header = xlNo
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ‘
    Sheets(“Sheet4”).Select
    Sheets(“Sheet4”).Range(“P4:Q28”).Value = Sheets(“Sheet1”).Range(“A2:B26″).Value
”””””””””’
‘1       Ascend
‘A   A   A
‘Prv No: Prv
      ‘
”””””””””’
    ‘NEXT—>
    
    Sheets(“Sheet1”).Range(“A1:A26”).Value = Sheets(“Sheet4”).Range(“A3:A28”).Value

End Sub
‘to be continued

Reply | Quote

This demo has the complete macro of what is suppose to happen, but preferably with a formula.

Reply | Quote

I don’t follow. Could you elaborate?
You haven’t answered the question of which cells give odd results. In fact I don’t even see any cells with the INDEX/Match Formula in it.

As to your latest posts:
I don’t understand how you expect a formula to do what the macro does. Which cells do you want formulas in and what should they calculate? What would the spreadsheet look like with NO Macro and what cells do you want formulas in, and what should they be calculating?

Steve

Reply | Quote

The last demo I posted gives a illustration of the referenced columns that are not Ascended
when the formula, =ROUND(($L$4-$J$4)*100/$L$4,2) calculates in each cell.

Next to it’s respective 2 columns are the Descending order of that range as demonstrated by using a macro.

Cell Ranges:
A4:A28 04:028
No: Prv
2 (4.52)
3 (6.45)
5 5.67
7 5.43
8 4.17
9 2.74
10 6.90
11 (2.44)
12 6.21

Becomes Ascended in Cell ranges:
P4:P28 Q4:Q28
No: Prv
3 (6.45)
2 (4.52)
11 (2.44)
9 2.74
8 4.17
7 5.43
5 5.67
12 6.21
10 6.90

Does a formula exist to Ascend Q4:Q28 and referenced from A4:A28, to be placed in P4:P28 and Q4:Q28 or do I have to use a macro as demonstrated ?

Thanks

Reply | Quote

In P4:
=IF(A4=””,””,INDEX($A$4:$A$28,MATCH(Q4,$O$4:$O$28,0)))

In Q4:
=IF(O4=””,””,SMALL($O$4:$O$28,ROW()-4))

Copy P$:Q4 down the columns.

Steve

Reply | Quote

Thanks,

Had to change
=IF(O4=””,””,SMALL($O$4:$O$28,ROW()-4))
to
=IF(O4=””,””,SMALL($O$4:$O$28,ROW()-3))

or it gave me #NUM! in P4 and Q4

XP

Reply | Quote

OOPS, typo. You are correct. The value should be 1 less than the first row…

Steve

Reply | Quote

Firstly, thanks for all your help.
There is one more minor detail I have never taken much notice of in the DOS program’s calculations

So far all calculations are “pound for pound”, identical and correct.
Except one column of calculated numbers from the same data input,I am not sure how it is significant by calculation.

The DOS program has a Menu, to View Graph Press, “A”
Above the graph are the calculations in the respective Ascending order.
——-,

In the Uploaded workbook I have identically illustrated in Columns AB to AI what the program looks like when pressing the “A” on the DOS menu.

The numbers in question are in Range AD4:AD12
I’m just curious to see how these have been calculated and would they be significant for the graph later on.

I cannot see the significance when all else is identical.

Thanks

Reply | Quote

It is the values:
=ROUND((M4-K4)*100/K4,2)
but instead of being Rows 4:12 with Nos 2,3,5,7,8,9,10,11,12, it has been reordered as the others with Nos:8,5,2,11,3,9,12,10,7

Steve

Reply | Quote

Somewhere I messed it up !

In Sheet 4, Ranges O4:Z28 it’s correct and it’s where I’m basing the formulas in Sheet 2 AM4:AX28
a real-time progressive scenario. This is actual.

I had to remove some of the $ ( absolutes ) within the formula so it references when copy/past formula from Sheet 4 to Sheet 2.
I’m mostly interested for now in the OVRL columns ( OVERALL)
I get the formula working OK in Rows 4 and 5, but below 4 and 5 it shows #N/A.
What am I missing or not seeing?

Thanks

:o:

PS
If there is a way to sort out the 8’s in Sheet2, Cell Range AN9:AN13 or through to AN28 be great to, but it won’t be part of the proposed graph, it’s more for visual.

Reply | Quote

Your formulas are not a series. In AQ some look at the locked range AP$4:AP$28 but then in AQ7 you go to the relative range in AP7:AP30 which will then change as the rows progress…

In AR4 you are looking at absolute rows AP$4:AP$28, but in AR5 it is relative and a different range AP5:AP29. In AR6:AR28 you are looking at the absolute $R$4:$R$28! It is no wonder it does not always find a match!.

I think if you copy AQ4:AR4 down the columns you will eliminate the errors, but I don’t know if that is what you want…

Steve
PS the 8s in Sheet2! AN9:AN13 come from the match finding the first zero. The index/Match presumes that there are no “ties”. If there are ties it finds the first match. With a formula, To eliminate them means you must eliminate the ties. One option would be to round everything to some number of decimals and then add the row value divided by something like 100000 to be a tie-breaker and make the numbers unique.
In AM4 something like:
=IF(AJ4=””,””,ROUND((AJ4-AH4)*100/AJ4,2)+ROW()/100000)

and copy it down the column, will eliminate the tying zeroes (as well as any other ties).

Reply | Quote

Fixed, all calculating correctly.

For the record/reference is the workbook

Thanks.

Reply | Quote

Back to this project after a break n brainstorm, it’s now for the next and final phase.

It’s the graph, but it needs to be progressive line graph. I have no idea on how to VBA a progressive graph, being the sort as a new column is populated with up to 25 rows Max, or 3 rows Min, the lowest 3 number are red lines, the rest are blue lines, with a black background.

The negatives numbers in the line graph need to go up, and the positive or O blue lines need to go downwards.

Can someone show me an example Wk.Book on how to do the basics using Excel 2010 please, I can then view some graph and code examples, improvise and test ?

Note, this graph scenario is real time update in sequence. ( That’s what the previous posts were for, to get to the graph side of things and visualize the “movements” )

Thanks.

Reply | Quote

Could you post some sample data and what you want the chart to look like exactly.

Steve

Reply | Quote

Steve,
Sure can, may take me a while, (new day job),
I”ll try to present something which is “uncharted territory” for me with graphs in Excel 2010.

Thanks

Reply | Quote

Ok, Steve

The WkBook has 3 sheets
Sheet 1 is named: “SEQUNCE with formula”
P-Var stands for, Percent Variable.
This sheet’s entries/populates was created because of the VBA earlier in this post.
It shows the Formula which calculates the difference in percent which I included manually for this illustration, but will include later within the process.
It’s the percent figures (P-Var) that are used for the graph lines.

Sheet 2 is named: graph reference
This is the same as “SEQUENCE …”, but I have manually edited by Ascending each column using the column where the formula is in SEQUENCE. I did this to illustrate what I’m trying to explain, or if need be to use as the reference point for the graph.

Cell Range A2 has 60m,(60 minutes), this is a real time calculation before the start of an event.

Cell C2 has 30m,(30 minutes), this is because there was no change in circumstances for 30 minutes.
In Cells W2 and Y2 it has 3M,(3 minutes), this is because there were 2 changes within the same minute. Therefore the process was updated twice.
Same goes with Cell AA2 and CC2 where it shows 2M,(2 minutes).
Therefore the process was Looping close to an hour at the time, populating a new series of numbers when there was a change.

The point is, the progressive graph lines would appear fluctuating along this time line, that is why I require a Graph with VBA that updates when a new column is populated.

The “Lowest” number, in this case in Cell AD3 -4.43 and it’s corresponding reference is 7
This is the most important, the graph to show those negative numbers in the Ascending order as red lines going up in the graph.
In this case it would show 7,6,5,15,12,11 in red lines above the center of graph as “in”, and the remaining number in blue lines going down below the center as the “out”s

Sheet 3: “GRAPH” is what the graph is suppose to look like when blank.
This is where the lines ought to appear progressively.
The negatives are Red lines need to go up, this is “In”
The Positives are Blue lines need to go down, this is “Out”
If there has been no movement along the timeline and the folrmula shows 0, then those lines are blue to.

There can be anywhere between 3 to 24 corresponding references, in this case there are 16. Even though the 3 is omitted, it was not included in the initial line up of 17 prior to start of the event.

Finally, if this workbook does not illustrate what I’m asking, then let me know and I’ll try to macro a simulator using VBA and some case scenarios.

Thanks

Reply | Quote

You have a blank chart. So I ask again: “Could you post some sample data and what you want the chart to look like exactly.”

What is the x-Axis and what is the Y-Axis, what datasets are to be used. Do you have the x and y data sets you want to plot, or must they be transformed in some way.

Steve

Reply | Quote

Steve, There may have to be some transformation, if that is the correct term.

If I was to track 1 row at a time, the X would start at Cell F4 and track a Y range
H4, J4, L4, N4, P4, R4, T4, V4, X4, Z4, AB4, AD4, AF4, AH4.

The chart wizard’s formula does not allow me to skip columns.
Formula shows as,

=’SEQUNCE with formula’!$F$4

Can it be something like,

=’SEQUNCE with formula’!$F$4 & H4, & J4 & L4 & N4 & P4 & R4 & T4 & V4 & X4 & Z4 & AB4 & AD4 & AF4 & AH4

?

Reply | Quote

If you define a named formula with the “refers to” as:
=IF(ISODD(COLUMN(Sheet1!G15:AH15)),NA(),Sheet1!G15:AH15)

Then use the name you give to that formula as the Y-range of a series in the chart, I think it should work.

Though if you have a lot of them, it may be easier to just create intermediate calculations to get a contiguous range and then plot that…

Steve
More cumbersome, but possible, is to define in the chart the y-range as (change the sheet name as appropriate):

Code:

=(Sheet1!$H$4,Sheet1!$J$4,Sheet1!$L$4,Sheet1!$N$4,Sheet1!$P$4,Sheet1!$R$4,Sheet1!$T$4,Sheet1!$V$4,Sheet1!$X$4,Sheet1!$Z$4,Sheet1!$AB$4,Sheet1!$AD$4,Sheet1!$AF$4,Sheet1!$AH$4)

Reply | Quote

Steve, thanks.

I manually plotted the series.

Re: uploaded Excel file: June2014-graph 2.zip

Sheets: Graph 2, is referenced from the Ascending sorted order in Sheets: SORTED.

I have added only the 3 referenced from last entry in column AD.
All along the time-line, ( rows) their percents fluctuated.
This is normal, it’s why I require the graph, to view the fluctuations.

The graph lines need to show negative values going upwards.
Excel naturally shows negative Values as downward-lines.

How do I get the referenced negative value lines to go upwards and the positive value lines to go downwards ?

Thanks

Re: uploaded screenshot of DOS programl file.
File Name:example 5 GRAPH 3.zip
I have included a screen capture from the DOS program to show those negative graph lines going up

Reply | Quote

I am still not sure what you want perhaps this is close.
I don’t understand how the DOS chart (the BMP) is plotted based on the 3 columns of numbers displayed, so I did nothing with this.

To reverse the Y-scale, got to that format axis and select “Values in reverse order”. This puts the negatives at the top and positives on the bottom.
37192-Capture

To create Graph2 in simple manner, I created an extract of what I think is the data you want (it seems to make your Graph2). I did reverse the scales (as you wanted), and I also added vertical gridlines, and also put the labels at the bottom of the chart (“High” on the reversed-Axis) since I thought it looked better (change as desired)

The key formulas and the setup are:
A2: 1, the first column for the X-Axis Labels
A3: =A2+2, the 2nd column is 2 columns later
As is copied from A4:A16 to get all the column numbers

B2:=INDEX(SORTED!$A$2:$AD$2,A2)
Copy B2 ti B3:B16 to extract every other column label

C1:C3 are 7,5,6 respectively to extract the ranges of those values in each of the column (change these as desired)

C2: =VLOOKUP(C$1,OFFSET(SORTED!$A$1,2,’Sorted Extract’!$A2-1,16,2),2,0)
Looks up the value in row 1 in the range from the appropriate column and gets the value to the right

Copy c2 to C2:E16. If desired you can copy more columns and enter additional numbers and then expand the chart.

If this is not what you want, you will have to provide more explicit details.

Steve

Reply | Quote

Steve,

I am still not sure what you want perhaps this is close.

yes, getting there.
-,

I don’t understand how the DOS chart (the BMP) is plotted based on the 3 columns of numbers displayed, so I did nothing with this.

The visual display of the 3 on the DOS chart are part of more calculations on another part of the program.
In that case it is the “preferred selection”.
Think of the “best 3 stocks and shares to buy or sell” in accordance with price fluctuations over a given space of time. Hence the 60m or the 2m may be 60 minutes before close of sale, or 2 minutes before close of sale, in principle.
-,

I’ll answer the rest your previous post after work and show some more GIF files from the DOS program of where all the lines originate from so to make it simpler to explain.
It’s easier for me to show visuals than try to find, correct-word-perfect-exact-meaning, questions.
But it is very close and correct so far.

Reply | Quote

I presume that means that you are not manually entering the 7,5,6. How is that determined? Is it just the top 3 items in the last column? Could the number of columns grow?

Steve

Reply | Quote

Steve, I have uploaded all the example screen shots from the DOS program.
The DOS program uses 5 columns of manual data entry. ( back in 1993 )
At that time there would be these numbers were viewed from a special channel on the TV and every minute or so there would be an update. Then the numbers were entered in the next column, 5 columns in total.

Then in 2000 the Internet and Excel came of age, the TV channel/s posted the same info on web pages, and voila, a macro does the rest using dynamic web query.

Today, this same info is limited and the web pages are full of blinking colors, it gets confusing to do the work. But the sum and the calculations basically remains the same.
That calculation is the difference in percent between 2 numbers.
So if at 11.55 AM a item was showing 10.00 and at 11.56 AM it showed 5.00, then that is -50% and the graph line goes up.

Between the latest entry along the series, the 3 with the “most” change in price are red lines going up all along the current series.
That means,
If 1,2 and 3 had the most drop in price percent wise, then those 3 will be exposed as red lines all along the graph.

In my graph I have used ALL the time line, that is every price update from which may use 15 columns, depending how many changes there are. I don;t think it is possible to make a graph as clear in the DOS program which uses a maximum of 5 columns where Excel can use as many as one chooses to.
This can be determined later on if it’s practical or not.

So I hope the BMP files of the screen shots shed some light of what I am trying to get here.

The web queries and all the accumulation of this data works fine, the formulas work fine, they match up with the DOS program’s percent output.

It’s just the graphing at this stage and then some automation/VBA macro because it’s real time progression, meaning the data starts at 1 column, therefore there is no percent calculation until the next column is populated after a web query update.

I’ve uploaded the same Workbook, but in my graph have populated the entire field, 15 columns.
I think by the looks of things for practical reasons, 7 columns Max, 2 up on the DOS program which can do 5.

Reply | Quote

Still confused. In my last post I added a sheet which extracted what I thought you wanted. Let’s start there. This is my best “guess” at what you want and nothing you added in this post changes my “understanding” (or I suppose “misunderstanding” may be a better term…)

As I indicated I only added three columns more can be added, and the numbers being grabbed (7,5,6) could be entered with formulas. So please detail: What is inaccurate, what it correct? What do you need changed: are there logic issues in what I did, do you need more data (more columns, more rows), does it need formulas to do something else, does it need to ignore things (variability in number of columns and rows), or what?

You need to walk me through what you have, what you will have etc.
[It seems to me that DOS BMPs are not very insightful for understanding. Perhaps I am mistaken, but the first column of data for the chart, seems to be the last point. But the BMP does not have the data for the other points so it is very confusing for trying to figure out what you want to plot]

Steve

Reply | Quote

I need a few days to make a simulator to show the progression and then point out with Msg Boxes where the graph begins and where it ends.

The only question I have at this point with your graph is in Column A from A3
Formula references the above cell range.
=+A2+2
=+A3+2
I don’t get that.

To explain,
The values in Sheets:SORTED A3:A18 is reference number of a product name or competitor.

So if the value in A3 is 1, it could also mean it has a name such as, “baked beans”.
“baked beans” has a reference number, in this case it’s 1.
A3 has Value 3, it could be the name of another product or competitor.

To explain further,
Sheets:SORTED is a manual Copy and Paste of Sheets:formula, except the formula columns have been omitted then each Column has been Ascended using it’s respective Sum from Sheets:formula.

Therefore,
Sheets:SORTED shows the progressive changes.
The very last entry, Sheets:SORTED (AC3:AC18 and Sheets:SORTED (AD3:AD18), the “top 3” is 7,6,5.
If you look at the DOS graph for similarities, it’s “top 3” are also pointed out, it’s graph line is Red.

Other than that so far your graph makes sense and have learnt much from it, not sure I understand the VLOOKUP formula or if this will work when VBA is added so the graph updates in line with the web query updates as the updates occur.

Back in a few days, ( work commitments) with a proper simulator as if it’s real-time, I can’t explain this in any other way.

Thanks Steve, it’s been a very cool learning experience, much appreciated.

Reply | Quote

In the ‘Sorted Extract’ sheet.
Col A gives the column number of interest from the ‘SORTED’ Sheet.
A2 has 1 (=Col A)
A3 has A2+2 [= 3 (=Col C)
A4 has A3+2 [= 5 (=Col E)

These are the columns you have labeled as “A” in row 1

Column B is the data for the X-Axis. It extracts the information from ‘SORTED’ row 2 for the column numbers given in col A of ‘Sorted Extract’. This is the 60M, 30M, 25M, etc

‘Sorted Extract’ C2:C16 grabs the various values from SORTED for what is in C1. So in ‘Sorted Extract’ C2, it looks in col 1 (from ‘Sorted Extract’ A2) to find the value of 7 in ‘SORTED’ Col A. It finds it in ‘SORTED’ A17 and looks at the next col (B17) to give the 21.39. In ‘Sorted Extract’ C3, it looks in col 3 (from ‘Sorted Extract’ A3) to find the value of 7 in ‘SORTED’ Col C. It finds it in ‘SORTED’ C8 and looks at the next col (D8) to give the -6.17, The same is done for the rest of the rows and Similarly in COls D & E.

Steve
PS. To get the Top 3 items from ‘SORTED’ Col AC, you can select ‘Sorted Extract’ C1:E1, type in the formula bar:
=INDEX(SORTED!$AC$3:$AC$18,{1,2,3})

Then confirm with ctrl-shift-enter.

Note that this puts 7,6,5 into C1:E1, respectively. This is different than your example dataset (June2014-graph 2.xlsx) which had them ordered 7,5,6 which is why I set it up to manually enter them: I could not see the logic in the order from the example so could not create a formula.

Reply | Quote

Steve, it took me a while, ( work n stuff ), but I finally got something I hope to get this progressive graph
sorted.

I tried to create a graph, plot the Values and Invert so the negative line go upwards, but for some reason the Tab on my ribbon is greyed out, it just won’t go into the black so i can select the Invert radio button.

Secondly, I have very little know how on graph formats, I simply need a multiple line graph.

The simulator I’ve uploaded is “as if” there is real-time web query.
I think it’s self explanatory as to where the Graph lines ought to start.
I tried macro record the same, but I at my end wits on this, I have done all I can.

When you open the Workbook, simple click the yellow macro button in A1, it will run through the process, in the VBA editor, F8, I hope to have made it so this question is self explanatory.

Much appreciated if this can be sorted.

Thanks

pete

Reply | Quote

Is this what you are after? It plots all the values from the top 5 of the current listing.

Steve
PS run your simulator (yw macro button) to see the plot based on the simulated values.

Reply | Quote

Thanks,

sdckapr
Is this what you are after? It plots all the values from the top 5 of the current listing.

Yes, 5 is better.

PS run your simulator (yw macro button) to see the plot based on the simulated values.

Ok, It may take a week or so to test run using various scenarios. ( moving house, may be offline)
The scenarios only differ in 2 ways.

1. The length, meaning there may be more columns beyond Column AY, ( still under sonstruction)
11M TOGO,( 11 minutes before the end of web query, or the event).
Sometimes it’s a lot more, 60M, 45M, but mostly from 20M through to 1M the updates are more frequent, therefore more Columns might become populated.

2. The Range downwards, between rows 4 to 28. It rarely gets to full, but it can, (MAX is 25 Rows).

It’s just these 2 variables that are constant, I hope was considered.

Other than that I think it’s basically there, I see how you have constructed the Graph and relevant formula in Sheets Chart Extract. Much to learn from.

Just one minor tweak,
This formula for example is suppose to show blank if unpopulated, so I thought.

Code:

=IF(formula!H4=””,””,(formula!H4))

But once there is a Value in another Cell, the entire undpopulated cell ranges with that type of formula shows, #VALUE!
I tried various methods of formula, but could not get it to “disappear” when not in use.

Reply | Quote

The length, meaning there may be more columns beyond Column AY, ( still under sonstruction)

For this you just need to add more rows to the data set in chart extract and expand the chart range to include them

Sometimes it’s a lot more, 60M, 45M, but mostly from 20M through to 1M the updates are more frequent, therefore more Columns might become populated.

This should not be an issue, the values are not hard coded, the formulas pull from the dataset. You just need to have the right columns in Chart Extractor col A. If your pattern was regular [eg eliminating the numbered columns T and AJ. I don’t see the purpose of them, it is just a duplicate of A. Why not freeze at B4 to keep A and rows 1-3 always visible?]

The Range downwards, between rows 4 to 28. It rarely gets to full, but it can, (MAX is 25 Rows).

Looks at 16 rows now, change the 16 to 25 in the formulas in Chart Extractor Cols D:H

Just one minor tweak,
This formula for example is suppose to show blank if unpopulated, so I thought.

It is not an issue with the workbook you attached or from the simulator. You will have to elaborate on when this is an issue.

Steve

Reply | Quote

Sometimes it’s a lot more, 60M, 45M, but mostly from 20M through to 1M the updates are more frequent, therefore more Columns might become populated.

This should not be an issue, the values are not hard coded, the formulas pull from the dataset. You just need to have the right columns in Chart Extractor col A. If your pattern was regular [eg eliminating the numbered columns T and AJ. I don’t see the purpose of them, it is just a duplicate of A. Why not freeze at B4 to keep A and rows 1-3 always visible?

[eg eliminating the numbered columns T and AJ.

My wrong, was put there during the build making sure the formulas lined up between sheets and columns. When I tried Split Screen at the time, I was not able to switch beteen sheet tabs, the split was in the way.
…but I “get” that now, the formulas in Chart Extract: Column A.
Can fix, or try.

–,

The Range downwards, between rows 4 to 28. It rarely gets to full, but it can, (MAX is 25 Rows).

Looks at 16 rows now, change the 16 to 25 in the formulas in Chart Extractor Cols D:H

Yes, I have some work to do, and get from demo over to the real thing.

On test it does exactly as required, the visual display of these fluctuations for statistical purposes,
there is no more to add.

I can tweak it from here and fix some “cosmetics”,
overall the numbers are correct when tested with pen to paper or Excel.

I would say this is now SOLVED.

Thank you Steve ,sdckapr
and W-Secrets.

Reply | Quote

Thank you Steve

You are very welcome. I am glad I could help.

Steve

Reply | Quote