Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

conditional cell changes and calculation

Posted on 2013-11-13
11
Medium Priority
?
197 Views
Last Modified: 2013-12-02
I have an Excel worksheet in which I want to begin by checking column 7 (G) for the presence of a 0 (zero). If it is a zero, change it to a 1 and then in column 9 (J) calculate the log base 2 ratio of column 8 (H) to column 7 (G).

Then, check column 8 (H) for the presence of a zero. If it is a zero change it to a 1, and calculate the log base 2 ratio of column 8 (H) to column 7 (G).

Then do this for every 12 columns; so the next columns to check would be column 20 (T) and column 21 (U) for the presence of 0's and calculate the log base 2 in column V (21). Then columns 32 and 33 are checked for 0's, and the log base 2 calculated in column 34, etc, until the end of the worksheet which can have a variable amount of columns.
0
Comment
Question by:vbaabv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39646914
Here is a macro that will calculate the log base 2 of your ratios. It wasn't clear in your question whether the formula goes only where a value was changed from 0 to 1, or in every row. I show both alternatives, but commented out the "every row" alternative.
Sub LogRatio()
Dim cel As Range, rg As Range
Dim i As Long, j As Long, jStart As Long, k As Long, nRows As Long, nCols As Long
With ActiveSheet
    Set rg = .UsedRange
    Set rg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1)      'First row contains header labels
    nCols = rg.Columns.Count
    jStart = .Range("G2").Column - (rg.Column - 1)
    For j = jStart To nCols Step 13
        On Error Resume Next
        For k = 1 To 2
            Do
                Set cel = Nothing
                Set cel = rg.Columns(j + k - 1).Find(0, LookAt:=xlWhole, LookIn:=xlValues)
                If cel Is Nothing Then Exit Do
                cel.Value = 1
                cel.Offset(0, 3 - k).FormulaR1C1 = "=LN(RC[-1]/RC[-2])/LN(2)"   'Use this statement if formula only in rows where 0 changed to 1
            Loop
        Next
        'rg.Columns(j).Offset(0, 2).FormulaR1C1 = "=LN(RC[-1]/RC[-2])/LN(2)"     'Use this statement if formula needed in every row
    Next
End With
End Sub

Open in new window

0
 

Author Comment

by:vbaabv
ID: 39686013
Hi byundt,

   Sorry, I thought I had responded to your script. I ran it a couple of weeks ago, but apparently I did not write a reply. Sorry for that.

    You script does very well, except it only works on columns G, H and I. It needs to also do the same for columns S, T and I, then AE, AF, and AG, (every 12 columns) etc. until the end of the worksheet (where the last information is located, which will be 6 columns to the right of the first of the last three columns to be worked on.)

 The log(2) calculation needs to be done only when there is a change of 0 to 1 in that row.
  If there is a zero in two adjacent columns, then I guess when the log(2) calculation is done the second time (after the second 0 is changed to a 1, the end result will be correct.  Log(2) will = 0.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39686028
The code is working in my test workbook. Please try it on the attached sample workbook.

If it is not working on your real workbook, please post a sanitized version so I can identify and fix the problem.

Brad
Log2Q28293694.xlsm
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:vbaabv
ID: 39686212
I tried it again on my worksheet (there are over six thousand rows, so it took a while) and it still does not seem to go beyond the first three columns, G, H and I.
 
   I have attached a test worksheet which I have pared down to 50 rows and have highlighted the cells in red that contain zeros that need to be changed to 1. You can also see, that the log(2) column, the first one being column I, the next one is column AF, etc., presently have either a '0', an 'inf', or a '#NAME?' wherever a log(2) calculation has to be made.

Thanks for your help.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39686401
No file was attached. Remember that you need to enter a description after attaching the file to make it stick.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39686405
I should also point out my understanding of your request:
If there is a 0 in either column G or H, turn it into a 1 and put the log(2) formula in column I.
Repeat with columns T and U, putting the formula in column V.
Repeat every block of 12 columns until the end of your data.
0
 

Author Comment

by:vbaabv
ID: 39687505
Sorry, again. I will have to wait until Monday before I can get access to the file I made.  

Thank you again for your efforts.

Yes, you do understand correctly.

  After thinking about this, I think that your script may in fact be working correctly; the problem being that the first set of three columns should be H, I and J with the log2 calculation in J. Then the next set of columns being T, U and V, and so on, every 12. This makes sense to me because T, U, and V are 12 columns away from H, I  and J and not from G, H, and I.  I think I mistakenly put columns G, H and I in my request, but I will have to wait until Monday to check it. So, your script could very well be working correctly, but I did not see it because I was looking at different columns. However, it will be Monday before I can check. If my suspicion is correct, I can probably tweak the script to work on the columns I need. In either case, I will notify you by early Monday afternoon.
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 39687511
You'll be wanting to play with statements 8 and 9 in my code:
    jStart = .Range("G2").Column - (rg.Column - 1)
    For j = jStart To nCols Step 13

Open in new window


The reference to cell G2 should be to the first cell that gets checked for a 0. Presumably, you'll change it to refer to cell H2.

The reference to Step 13 should be to the number of columns in each repeating block. Presumably, you'll change it to 12 so you increment from column H to column T.

Brad
0
 

Author Comment

by:vbaabv
ID: 39687518
Brad,

  Thank you very much for the help. The more I think of it, the more my suspicion is that your code works and I made a mistake in the original request. However, I don't have one of those worksheets home with me and I just want to make sure. I will try it out Monday morning and reply I soon as I can.
0
 

Author Comment

by:vbaabv
ID: 39690783
Hi Brad,

  My suspicion was wrong and the first column is column G. So, the first three columns to work on are G, H and the log2 calculation in column I.

  However, I just changed the '13' to '12' in the following line:

For j = jStart To nCols Step 13

and everything works fine !

The mistake was in my original request, I stated that the next set of three columns would be T, U and V, but it should have been S, T and U which are 12 columns away from G, H and I.

Great job !  Thank you very much,

Matthew
0
 

Author Closing Comment

by:vbaabv
ID: 39690785
Nice job.  Thank you.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question