# 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 forVariablesY (set to cell A1)X (set to cell B1IncY (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.

✔ 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 MethodEnter 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\$1In B2, enter this formula:=B1+1In C2, enter this formula:=IF(A2>=B2,COUNTA(\$A\$2:A2),"")Drag all 3 formulas down until a value appears in Column C.

#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?

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 MethodEnter 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\$1In B2, enter this formula:=B1+1In C2, enter this formula:=IF(A2>=B2,COUNTA(\$A\$2:A2),"")Drag all 3 formulas down until a value appears in Column C.

Report •