recognizing the pattern within a string, then lookup and calculate calories

[gesser]

I'm trying to create an exel file that will calculate eaten calories.


For example, when I enter a number (or series of numbers), that represents a food - Exel looks up this number and calculates calories.


Table 1. contains all food entries as well as calories:


Nr === Food == Calories

-----------------------

1 === Bred == 100 kcal

2 === Cheese == 200 kcal

3 === Cola == 300 kcal


Table 2. is where calculations are done:


Date == Eaten Food == Total Calories

----------------------------------

21 nov == 1+2(2)+3(1/3) == 100+200*2+300/3=600 kcal


600 kCal is the result that I'm trying to achieve. Whole process is following:

* we enter numbers separated by +(plus)

* number pattern is: X or X(Y), where X is a Number from table 1. and Y is the multiplier

* exel lookup each number

* calculates calories for each food

* sums everything up

* shows the result


Here is how Exel looks like:

http://oi46.tinypic.com/2ntb51h.jpg


Sorry, i tried to upload an Exel file, but my favorite file-hosting service is down on maintenance.


Any advice or help on how to achieve 600 kCal result (as in example) will be appreciated!

 

[Luke M]

Before getting too far, you might want to check out some of Debra's files which sounds very similar. Here's the first link:

http://blog.contextures.com/archives/2010/01/06/excel-calorie-counter/


And then search for "calorie counter" on this page to find some other templates:

http://www.contextures.com/excelfiles.html

 

[gesser]

ok, it took me 5 hours and now the job is done

It's 3AM in the morning and the studies will start in 4 hours, but the feeling is amazing! In case if someone is interested - I used Excel VBA:

[pre]

Function CalcCalories(cell As Range, cell2 As Range)
Dim splited As Variant
Dim i As Integer
Dim Calories As Double
Dim result As Double
Dim number As Integer
Dim multiplier As Double
Calories = 0
result = 0

splited = split(cell.Value, "+")
For i = 0 To UBound(splited)
If InStr(splited(i), "(") > 0 Then
openingParen = InStr(splited(i), "(")
closingParen = InStr(splited(i), ")")

number = Mid(splited(i), 1, openingParen - 1)

Dim multiplierString As String
multiplierString = Mid(splited(i), openingParen + 1, closingParen - openingParen - 1)

If InStr(multiplierString, "/") > 0 Then
openingParenMult = InStr(multiplierString, "/")
delimoe = Mid(multiplierString, 1, openingParenMult - 1)
castnoe = Mid(multiplierString, openingParenMult + 1, Len(multiplierString) - openingParenMult)
multiplier = delimoe / castnoe
Else
multiplier = multiplierString
End If
Else
number = splited(i)
multiplier = 1
End If
Calories = LoopRange(cell2, number)
result = result + (Calories * multiplier)
Next
CalcCalories = result
End Function

Function LoopRange(cell As Range, searchFor As Integer) As Double
Dim maxY As Integer
maxY = cell.Columns.count
Dim currentColumn As Integer
Dim result As Integer
result = 0
For i = 1 To cell.Rows.count + 1
Dim curvalue As Integer
curvalue = cell(i, 1)
If curvalue = searchFor Then
Dim Calories As Integer
Calories = cell(i, maxY)

result = Calories

Exit For
End If
Next
LoopRange = result
End Function

[/pre]

Cell1 - line with values (For example: "1+2+5+1(1/25)"). Pattern: X(Y) or X. X - Food number, Y - multiplier (can be 1/2, 5, 1/32 etc).

Cell2 - table with the data(The first column - Food Numeration, the last column - Calories)


to call out the function you must put into a cell: "=CalcCalories(G9,$B$5:$D$8)" or something similar to this. The program will automatically calculate calories for all the food that you've eaten during the day!!!!!


yesssssss!! more coffee please