Solved Need help designing an algorithing.

July 10, 2015 at 15:22:22
Specs: Windows 7
Can't wrap my head around this problem. Here's what I'm going for

Variables

Y (set to cell A1)
X (set to cell B1
IncY (set to 3)
IncX (set to 1)
Days(result)

What I want to know is is Y increases by 3 daily and X increases by 1 daily then how many days will it take for Y to be equal or greater than X.

IncY should be set to another cell at well so I can play around with how much Y increases but default it 3 for the moment.


See More: Need help designing an algorithing.

Report •


✔ Best Answer
July 11, 2015 at 23:19:35
Here is some VBA code that should get the job done.

I've offered 3 different ways to assign an Incremental value to Y: a hardcoded value, a reference to a cell and an InputBox to get the value from the user.

I've offered 2 different output methods: Place the count in a cell or present a Message Box to the user.

Remove/replace the comment symbols ' where appropriate to test the various options.

After the code, I've offered a brute force way to get the answer in an Excel spreadsheet.

BTW, if you are going to playing around with VBA, you should probably review this tutorial:

http://www.computing.net/howtos/sho...

VBA Method

Sub CountDays()
Dim X, Y, IncX, IncY
'Initialize variables
  Y = Range("A1")
  X = Range("B1")
  IncX = 1
 
'Hardcode incremental value for Y
    IncY = 3
'Use cell for incremental value for Y
    'IncY = Range("C1")
''Get incremental value for Y from user, Exit if Cancelled
    'IncY = Application.InputBox("Enter Increment Value For Y", _
                                 "Incremental Value", Type:=1)
    'If IncY = False Then Exit Sub

'Loop while incrementing Y and X, count loops
 Do While Y < X
  X = X + IncX
  Y = Y + IncY
    cntDays = cntDays + 1
 Loop
'Set cntDays = 0 if Y is initially >= X
'This avoids a blank output
    If cntDays = "" Then cntDays = 0
'Put Day Count in D1
    Range("D1") = cntDays
'Present Day Count to user
    'MsgBox "It took " & cntDays & _
            " Days For Y to be equal to or greater than X", _
             Title:="Day Count Result"

End Sub

Spreadsheet Method

Enter your Y, X and IncY values in A1, B1 and C1 respectively. In each of the following formulas, make sure you use the $ exactly as shown.

In A2, enter this formula:

=A1+$C$1

In B2, enter this formula:

=B1+1

In C2, enter this formula:

=IF(A2>=B2,COUNTA($A$2:A2),"")

Drag all 3 formulas down until a value appears in Column C.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
July 10, 2015 at 17:28:08
First, I assume that you are looking to use a macro, correct?

Doesn't there need to be restrictions related to what A1and B1 can contain? If A1 is greater than B1, then you're done before you even start.

BTW this sounds very much like a homework problem. Is it?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 10, 2015 at 20:50:10
Haha no its not a homework problem. I haven't been in school for quite sometime. This is actually for a very nerdy thing. It going to be a general formula for when I'm grinding certain things in video games. I like to make spreadsheets to track my progress through out different games or event.

This one is particular it to track how many events I can complete to get my ratio of 1 event per day. I have this solved already in a long way where I have an array where the day increased by 1 and the events complete increases by whatever I set it at (3 is the goal) and the array increases until events complete >= days. Then I just use a countif formula to count how many cells have data in a particular column.

I'm looking for a way to streamline it so I can see how many days it will take to me to reach my goal if I complete 3 events a day, It should be flexible that I can change it to 4 events a day and see the new result.


Report •

#3
July 10, 2015 at 20:52:06
Logically I can work this out but I'm not sure what base functions to use in excel to achieve the goal or the syntax to code it in VBA.

Report •

Related Solutions

#4
July 11, 2015 at 23:19:35
✔ Best Answer
Here is some VBA code that should get the job done.

I've offered 3 different ways to assign an Incremental value to Y: a hardcoded value, a reference to a cell and an InputBox to get the value from the user.

I've offered 2 different output methods: Place the count in a cell or present a Message Box to the user.

Remove/replace the comment symbols ' where appropriate to test the various options.

After the code, I've offered a brute force way to get the answer in an Excel spreadsheet.

BTW, if you are going to playing around with VBA, you should probably review this tutorial:

http://www.computing.net/howtos/sho...

VBA Method

Sub CountDays()
Dim X, Y, IncX, IncY
'Initialize variables
  Y = Range("A1")
  X = Range("B1")
  IncX = 1
 
'Hardcode incremental value for Y
    IncY = 3
'Use cell for incremental value for Y
    'IncY = Range("C1")
''Get incremental value for Y from user, Exit if Cancelled
    'IncY = Application.InputBox("Enter Increment Value For Y", _
                                 "Incremental Value", Type:=1)
    'If IncY = False Then Exit Sub

'Loop while incrementing Y and X, count loops
 Do While Y < X
  X = X + IncX
  Y = Y + IncY
    cntDays = cntDays + 1
 Loop
'Set cntDays = 0 if Y is initially >= X
'This avoids a blank output
    If cntDays = "" Then cntDays = 0
'Put Day Count in D1
    Range("D1") = cntDays
'Present Day Count to user
    'MsgBox "It took " & cntDays & _
            " Days For Y to be equal to or greater than X", _
             Title:="Day Count Result"

End Sub

Spreadsheet Method

Enter your Y, X and IncY values in A1, B1 and C1 respectively. In each of the following formulas, make sure you use the $ exactly as shown.

In A2, enter this formula:

=A1+$C$1

In B2, enter this formula:

=B1+1

In C2, enter this formula:

=IF(A2>=B2,COUNTA($A$2:A2),"")

Drag all 3 formulas down until a value appears in Column C.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question