PDA

View Full Version : Solved: Complicated calculations



perhol
10-04-2007, 05:51 PM
First of all, I am sorry that my english is so poor!:dunno

I am about to make a sheet that requires som complicated calculations.

In cell B8 i enter a value between 4 and >22. Depending on that value the sheet should calculate a value in cells E8 and C14.

The value in C14 can only be 30 minutes, 1 hour or 2 hours and gives the time for next control.

Values, entered in B8 should be stored in a list on another sheet (valuesheet) in the same file. First value entered should be stored in A1 on the valuesheet. Following values should be stored in next empty cell in column A

The value calculated in cell E8 is a decimal value with 1 decimal depending on the following conditions:

If the first value entered in B8 is 8.3 trough 12 E8 should be 2 and C14 should be 1 hour.
If the first value entered in B8 is 12.1 trough 22 E8 should be 4 and C14 should be 1 hour.
After first value in B8:
If B8 is less or equal to 4 E8 should be 0 (zero) and C14 should be 30 minutes.
If B8 is 4.1 trough 4.5 E8 should be reduced with 2 if currently the value in B8 is bigger than 5 and C14 should be 30 minutes.
If B8 is 4.1 trough 4.5 E8 should be reduced with 0.5 if currently the value in B8 is less or equal to 5 and C14 should be 30 minutes.
If B8 is 4.6 trough 8.2 E8 should not change and C14 should be 2 hours.
If B8 is 8.3 trough 10 and the value is falling E8 should not change and C14 should be 1 hour.
If B8 is 8.3 trough 10 and the value is raising E8 should raise with 1 and C14 should be 1 hour.
If B8 is 10.1 trough 14 and the value is falling E8 should not change and C14 should be 1 hour.
If B8 is 10.1 trough 14 and the value is raising E8 should raise with 1.5 and C14 should be 1 hour.
If B8 is 14.1 trough 22 E8 should raise with 2 and C14 should be 1 hour.
If B8 is equal or more than 22 E8 should be "Individual evaluation" and C14 should be empty.
If B8 is 4.6 trough 14 and the value is falling with more than 50% E8 should be reduced with 50% and C14 should be 1 hour.

All of this is far more complicated than what i have made in excel before. Please help.:help

figment
11-19-2007, 01:58 PM
If B8 is 4.1 trough 4.5 E8 should be reduced with 2 if currently the value in B8 is bigger than 5 and C14 should be 30 minutes.
If B8 is 4.1 trough 4.5 E8 should be reduced with 0.5 if currently the value in B8 is less or equal to 5 and C14 should be 30 minutes.
If B8 is 4.6 trough 8.2 E8 should not change and C14 should be 2 hours.
If B8 is 8.3 trough 10 and the value is falling E8 should not change and C14 should be 1 hour.
If B8 is 8.3 trough 10 and the value is raising E8 should raise with 1 and C14 should be 1 hour.
If B8 is 10.1 trough 14 and the value is falling E8 should not change and C14 should be 1 hour.
If B8 is 10.1 trough 14 and the value is raising E8 should raise with 1.5 and C14 should be 1 hour.
If B8 is 14.1 trough 22 E8 should raise with 2 and C14 should be 1 hour.
If B8 is equal or more than 22 E8 should be "Individual evaluation" and C14 should be empty.
If B8 is 4.6 trough 14 and the value is falling with more than 50% E8 should be reduced with 50% and C14 should be 1 hour.


well up to here i was folowing you. at this point i need you to clarify a few things.

first you have to conditions that relly on B8 to be >=4.1 and <=4.5 and one of you varibles is determined by weather or not B8 is larger then 5 which it can not be if its <=4.5.

second, you start talking about weather or not B8 is falling or Rising, but you need something to compare it to to find if its falling or rising. Are we comparing B8 to B7 or some such? with out a Comparison its imposible to tell if its Rising or Falling.

lastly where is the Value for B8 coming from? it sounds like its changing, but what is changing it? and how often is it changing?

perhol
11-19-2007, 03:58 PM
Well, i may have been to quick about forming the question.

Values entered in B8 should be stored in a sheet (named valuesheet)in the same file in column A.
If no previus values exist in column A on the sheet valuesheet only the value in C14 can be found.
If values exist in column A on the valuesheet it can be determined if the value is falling or raising.

Also i have expressed some lines wrong.

"If B8 is 4.1 trough 4.5 E8 should be reduced with 2 if currently the value in B8 is bigger than 5 and C14 should be 30 minutes" is wrong.

It should be "If B8 is 4.1 trough 4.5 E8 should be reduced with 2 if the value in last cell in column A on the valuesheet is bigger than 5 and C14 should be 30 minutes".

"If B8 is 4.1 trough 4.5 E8 should be reduced with 0.5 if currently the value in B8 is less or equal to 5 and C14 should be 30 minutes." is wrong.

It should be "If B8 is 4.1 trough 4.5 E8 should be reduced with 0.5 if the value in last cell in column A on the valuesheet is less or equal to 5 and C14 should be 30 minutes.

Hope this clarifies it.

figment
11-20-2007, 11:40 AM
here is a quick example.
7337

the code is farly repetitive

perhol
11-23-2007, 10:33 AM
quick example was just what i needed.
Thankyou figment :hi:

mdmackillop
11-25-2007, 04:30 AM
While all the If statements work fine, it's easier for this to use Select
Case. The Case statement exits after a True value is processed so you don't have to state upper and lower limits each time. This is part code only, based on figments solution

Select Case b8
Case Is <= 4
setandlog logrow, 0, 0.5
Case Is <= 4.5
If .Range("A" & logrow - 1) > 5 Then
setandlog logrow, .Range("B" & logrow - 1) - 2, 0.5
Else
setandlog logrow, .Range("B" & logrow - 1) - 0.5, 0.5
End If
Case Is <= 8.2
setandlog logrow, .Range("B" & logrow - 1), 2
Case Is <= 10
If .Range("A" & logrow - 1) < b8 Then
setandlog logrow, .Range("B" & logrow - 1) + 1, 1
Else
setandlog logrow, .Range("B" & logrow - 1), 1
End If
Case Is <= 14
If .Range("A" & logrow - 1) < b8 Then
setandlog logrow, .Range("B" & logrow - 1), 1
Else
setandlog logrow, .Range("B" & logrow - 1) + 1.5, 1
End If
Case Is <= 22
setandlog logrow, .Range("B" & logrow - 1) + 2, 1
Case Else
'Do something else
End Select

perhol
11-25-2007, 04:55 AM
>mdmackillop
What in figments kode should this replace?

figment
11-26-2007, 07:03 AM
i realize that it can be done with the select case, i just dont like it. with the if statments you could leave holes in your input ranges, where with the select case method you can not. but thats just my personaly opinion

this is the code with the select case stament

Sub checking()
Dim logrow As Long
Dim b8 As Range
Set b8 = Worksheets("Main").Range("B8")
With Worksheets("log")
If .Range("A2") = "" Then
.Range("A2") = b8
If b8 >= 8.3 And b8 <= 12 Then
setandlog 2, 2, 1
ElseIf b8 >= 12.1 And b8 <= 22 Then
setandlog 2, 4, 1
Else
.Range("A2") = ""
End If
Else
logrow = .Range("A1").End(xlDown).Row + 1
.Range("A" & logrow) = b8
Select Case b8
Case Is <= 4
setandlog logrow, 0, 0.5
Case Is <= 4.5
If .Range("A" & logrow - 1) > 5 Then
setandlog logrow, .Range("B" & logrow - 1) - 2, 0.5
Else
setandlog logrow, .Range("B" & logrow - 1) - 0.5, 0.5
End If
Case Is <= 8.2
setandlog logrow, .Range("B" & logrow - 1), 2
Case Is <= 10
If .Range("A" & logrow - 1) < b8 Then
setandlog logrow, .Range("B" & logrow - 1) + 1, 1
Else
setandlog logrow, .Range("B" & logrow - 1), 1
End If
Case Is <= 14
If .Range("A" & logrow - 1) < b8 Then
setandlog logrow, .Range("B" & logrow - 1), 1
Else
setandlog logrow, .Range("B" & logrow - 1) + 1.5, 1
End If
Case Is <= 22
setandlog logrow, .Range("B" & logrow - 1) + 2, 1
Case Else
'Do something else
End Select
End If
End With
End Sub

perhol
11-26-2007, 01:55 PM
So the difference is :

If b8 <= 4 Then is replaced with Select Case b8
Case Is <= 4

ElseIf b8 >= 4.1 And b8 <= 4.5 Then is replaced with Case Is <= 4.5

ElseIf b8 >= 4.6 And b8 <= 8.2 Then is replaced with Case Is <= 8.2

...and so on.

Case looks more elegant and, in large files, 'case' code is quicker because code is shorter and it exits after a true value is found?

For this time i'l stick with figment's code.
But i will save mdmackillop's for learning.
Thankyou

mdmackillop
11-26-2007, 03:02 PM
Select Case can also be used with grouped tests. Check out the VBA Help file.