473,554 Members | 2,877 Online

# convert the excel formula into a Access 2010 expression

3 New Member
I created a table that has a field name InTime and a field name OutTime with a calculated field name TotalTime. Now I need to create a calculated field name TotalUnits.
I need to convert the excel formula into a Access expression. I’m including the numbers of minutes that equal to a unit (if this helps).

Number of Minutes in Military Time Units
0.13 minutes through 0.37 minutes = 1 unit
0.38 minutes through 0.62 minutes = 2 units
0.63 minutes through 0.87 minutes = 3 units
0.88 minutes through 1.07 minutes = 4 units
1.08 minutes through 1.37 minutes = 5 units
1.38 minutes through 1.62 minutes = 6 units
1.63 minutes through 1.87 minutes = 7 units
1.88 minutes through 2.13 minutes = 8 units

This is the excel formual:
=IF(I8>1.88,"8" ,IF(I8>1.63,"7" ,IF(I8>1.38,"6" ,IF(I8>1.13,"5" ,IF(I8>0.88,"4" ,IF(I8>0.63,"3" ,IF(I8>0.38,"2" ,IF(I8>0.12,"1" ,"0"))))))))
Dec 20 '10 #1
10 5131
8,834 Recognized Expert Expert
Expand|Select|Wrap|Line Numbers
1. =IIf(I8 > 1.88, "8", IIf(I8 > 1.63, "7", IIf(I8 > 1.38, "6", _
2.      IIf(I8 > 1.08, "5", IIf(I8 > 0.88, "4", IIf(I8 > 0.63, 3, _
3.      IIf(I8 > 0.38, 2, IIf(I8 > 0.12, "1", "0"))))))))
Dec 21 '10 #2
Cayetano
3 New Member
Thank you for your quick response, I’ve tried the expression with no results. I keep getting the following error: The expression could not be saved because its result type, such as binary or NULL, is not supported by the server. I have no idea what that means.

I did change the [I8] to the field name TotalTime but it didn’t work. Am I doing something wrong?
Dec 21 '10 #3
8,834 Recognized Expert Expert
Post all the SQL that you have so far.
Dec 21 '10 #4
akselo
21 New Member
To see if I understand--you are running an update query in access, or are you working in excel with linked data?

If you are working in access, you can use the IIF function in a nested way to account for each group (be sure to include the name of the field name in access and not the cell reference from excel), but I often find it is easier to insert a module and go with the select case statement , like so:

Expand|Select|Wrap|Line Numbers
1. Function group(minutes As Double) As Integer
2.
3. Select Case minutes
4.     Case 0.13 To 0.37
5.         group = 1
6.     Case 0.3700001 To 0.62
7.         group = 2
8.     Case 0.6200001 To 0.87
9.         group = 3
10.     Case 0.8700001 To 1.07
11.         group = 4
12.     Case 1.0700001 To 1.37
13.         group = 5
14.     Case 1.3700001 To 1.62
15.         group = 6
16.     Case 1.6200001 To 1.87
17.         group = 7
18.     Case 1.8700001 To 2.13
19.         group = 8
20.
21. End Select
22. End Function
23.
This function can then be called from the update query interface like so:
Expand|Select|Wrap|Line Numbers
1. update tblSource set TotalUnits=group(time);
2.
Dec 21 '10 #5
Rabbit
12,516 Recognized Expert Moderator MVP
If I am understanding the math correctly, can't you just use units = Int((minutes - 0.13) / 0.24) + 1
Dec 21 '10 #6
8,834 Recognized Expert Expert
@Rabbit - Very interesting approach, what is the logic behind it?
Dec 22 '10 #7
Rabbit
12,516 Recognized Expert Moderator MVP
I had only taken a cursory glance at the description before coming up with my formulization. I had originally thought the numbers were discrete but it turns out that is not the case. The formula then, according to the Excel formula should be units = Int((time + 0.12) / 0.25) because each unit block is 0.25 minutes long starting at 0.13. Well... I say 0.13 even though the Excel formula says 0.12 because it seems to be a discrepancy with the rest of the formula. If it really does start with 0.12 then the formula doesn't work so I'm hoping that was a typo.

However, if the formula is correct, then it may result in negative numbers and numbers larger than 8, which may be something the OP doesn't want.
Dec 22 '10 #8
8,834 Recognized Expert Expert
Bear with me, Rabbit. Don't the following Unit Blocks negate the assumption?
Expand|Select|Wrap|Line Numbers
1. '0.88 minutes through 1.07 minutes = 4 units - {19 Minutes}
2. '1.08 minutes through 1.37 minutes = 5 units - {29 Minutes}
Dec 22 '10 #9
Rabbit
12,516 Recognized Expert Moderator MVP
That's because he typed it wrong. In his formula, it's 1.13 and not 1.08. In that case it works. The only time it doesn't work is that in his formula, it starts with 0.12 and not 0.13. I'm hoping that too was a typo and he meant to start with 0.13.
Dec 22 '10 #10

### Similar topics

 0 1906 by: tag | last post by: Hi, I have a problem in that I need to be able to parse excel formula's and evaluate them to get results. Python is very well suited for this as it has WONDERFUL data handling capabilities. My question is : Is there an excel formula parser out there? and if not - does anyone have an idea on where to start ? 6 7126 by: alphaomega3 | last post by: I have a formula used in Excel that I am trying to convert for use in a query. the original formula is: =IF(AND(#REF!>=0,#REF!<=66800),(#REF!*((0.7-1)/(66800-0))+1),IF(AND(#REF!>66800,#REF!<=500000),((#REF!*((0.4-0.7)/(308000-66800))+0.783085)),0)) I have attempted the following but keep getting syntax errors..... tell me what I am doing... 3 4234 by: skiddle | last post by: I've got an Excel formula that calculates the total business hours that a help desk ticket is open. As one would expect, it factors out holidays and non-work hours. What would be the corresponding formula in Access? ... 2 2910 by: welshkaiboy | last post by: Due to the size of the data I have to manipulate I need to apply a excel formula in access which determines date of manufacture from a serial number 716001 so I use =VALUE(DATE(VALUE(MID(A1,3,1))+2000,1,1))+VALUE(MID(A1,4,3))-1 which gives 1/1/2006 How can I get this formula to work in access I know that the field name replaces the cell... 2 5540 by: rtilson | last post by: I am not sure why excel and access use the same method in formula. I am not access expert but been trying to figure it out. Here is the excel formula that I would like to use in access expression =ROUND((A2-5148000)*1.000440935+48000,IF(MOD(A2,1)=0,0,3)) I have tried using is the coorinate 1 4572 by: phill86 | last post by: Hi, I have an access 2010 database that I want to convert to an SQL Server database and I need to know if the data macros in access will still work in the SQL database or will i have to create triggers in the SQL Sever database Many Thanks Phill 0 1426 by: Lina Arraiz | last post by: I'm trying to export an Excel 2003 spreadsheet to Access 2007. I need to calculate the time elapsed between column D (START) and column E (END) with the results appearing in column F (TIME). The Excel formula, which is placed in column F, goes like this: =24*(IF(E2>D2,D2-E2)) Exactly what language do I use in Access and where do I put it in... 3 2003 by: david sherman | last post by: Does software converting excel to access with financial functions exist? I need to take several fixed income portfolio run manually from excel and create a database. The excel relies heavily on excel functions such as yield formula and durations formula. Any software that assists in conversion since i am not an access programmer? 2 3708 by: Geraldpauig | last post by: Hi there, need help in converting the excel fomulas to ms access. =IF(C2<=0,IF(SUM(B\$2:B3)