Excel Formula Help

Nov 22, 2004

Greetings,

This is my first try at asking for help on the net. I hope I do it correctly.

I have an Excel spreadsheet that has three columns. One column has numbers and two have text. I have about 15,000 entries (records).
The number column is the week of the year. It contains the WEEKNUM formula Sun-Sat (1 through 52 based on the date)
One text column contains event descriptions. One text column contains names.

What I want is a count of each description for each of the 52 weeks. (i.e. week 36 there were 120 "traffic accidents", 300 "traffic citations", 13 "assaults", 1 "armed "robbery", etc. etc.. I have been surfing and tried every formula that seems to say it will do this but nothing works.

I designed a special spreadsheet that looks at the "master" spreadsheet for the data and I had hoped would keep a running total on all descriptions for each week. I am self taught in Excel and need a no brainer. And yes, I now know excel is not a data base program.

Jim the data guy

View Replies


ADVERTISEMENT

Excel Formula Help

Sep 7, 2004

Need help with basic formula.

In column A, I have a list of dates going down the page.

In column B, I have the number of hours worked on each day.

I need a formula that will give me a total (sum) from column B for only the last 30 days as well as 90 and 365 days.

Thanks

View 1 Replies View Related

Excel - If And Formula

Mar 28, 2007

Hi,
Sorry to be a pain but I have not used this in a while and need some assistance - eg


I need to create an If And statement to formulate in bonus paid (Cell B8) - if number of new customers greater than 5 (cell B2), and the sales value (cell B5) is greater than 100,000, then return the message, "bonus paid" otherwise return "no bonus"

Can any one help?

Thankyou

View 1 Replies View Related

Convert Excel Formula For Use In Access

Feb 14, 2008

I have an excel worksheet with the following formula

=IF(b40<50000,b40*0%,IF(b40<100000,b40*2.5%,b40*5%))

and I would like to convert it for use in access.

I came up with the following:

=iif([TOTAL]<50000,[TOTAL]*0%,IIF([TOTAL]<100000,[TOTAL]*2.5%,[TOTAL]*5%))

I get the following error: "The expression you entered contains invalid syntax.

You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it with quotation marks."

I am probably missing something very simple here, but I have been unable to come up with a solution.

Any help would be greatly appreciated.

Regards,

Michael

View 3 Replies View Related

Translate An Excel Formula Into Access

Aug 8, 2005

In my worksheet there are 3 columns; 1. Date Completed (J), 2. New Review Date(K) and 3. Status(L)

I'm currently migrating from excel to access, the finishing tounces are needed, like the status.

The following excel function I want to convert to access

=IF(K1="","Outstanding",IF(K1<TODAY(),"Outstanding","Complete"))

(K1= Next Review Date)

I did do a search, but I couldn't really find anything.

How do I convert this please?

View 1 Replies View Related

Excel Formula To Access Query

Jan 11, 2006

Hi does any body no the access syntax to create this excel formula

=POWER(SQRT(L5/10)*47.1,2)/3769.9

Into access

Thanks

John

View 2 Replies View Related

Moving Excel Formula/expression To Access

Jun 1, 2005

I have 5 fields(First,Second,Third,Sum,Sum1). The Sum field is the sum of the first, second and third. In excel I am using the following formula/expression to calculate the Sum1 field =IF(E1<0,E1+10,E1+0 - and it brings back single digit.
e.g.
First=3, Second=5, Third=8, Sum=16, Sum=6
Can I do this in an Access query, if so how?

Thanks in advance for any help.

View 1 Replies View Related

Trying To Import Or Recreate A Multiplier Formula From Excel To Access

Aug 9, 2005

Really need help on this one, here is an excel expression:

X Multipliers
$ ValueMultiplier

is less than5then *3.8
is less than50*3.2
is less than100*2.8
is less than200*2.4
is less than500*2
is greater than500*1.8

so if price is less then 5 then multiply by 3.8 and so on. In excel it's in a separate worksheet, and other worksheets linked to it and adjust their Sale Price from the Cost column. Someone else created this spreadsheet, and i couldn't find a multiple discount help in access. I tried creating a query
=([PriceBook_OLD].[Cost]<=5)*3.8 Or ([PriceBook_OLD].[Cost]<=50)*3.2 Or ([PriceBook_OLD].[Cost]<=100)*2.8 Or ([PriceBook_OLD].[Cost]<=200)*2.4 Or ([PriceBook_OLD].[Cost]<=500)*2 Or ([PriceBook_OLD].[Cost]>500)*1.8
but it doesn't work. Please help!!!!
Thank you very much

V.

View 14 Replies View Related

Queries :: Pass A Formula Form A Query To Excel

Apr 5, 2013

Access 2003
Excel 2003

I have a routine that exports the results of a query to an Excel file. Is it possible to input the formula into the query so that the Excel values calculate?

This is the formula I am trying to pass to the "AZ" column of the Data tab

Code:
MyCalc::"IF(T2="","0",TODAY()-T2)"

View 1 Replies View Related

Modules & VBA :: Add Formula To Code / Export To Excel Table

Sep 25, 2014

I have a report exporting to excel using late binding techniques. When exported into excel i have numbers for 1,2,3,4 tblPreSiteSurveys. PreSiteSurvey Stop TheClockReason entitie and I am trying to either change the numbers here casting from int to string

1 = a
2 = b

looping through the record set, Or i have a blank field in my SQL for the column "P" and adding the formular to that column, but it only goes into the first row of the record set, which is a expandable table.

On Error GoTo Command29_Click_Err
'Utilergy Master Update report
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim UserDate As Date

[code]...

View 12 Replies View Related

Queries :: Turning Excel Formula To Criteria In A Query

Jan 7, 2015

=TEXT(TRIM(MID($A2,5,2) &" "&LEFT($A2,3)&" "&MID($A2,8,10)&" "&RIGHT($A2,2))+0,"dd/mm/yyyy hh:mm:ss ")

Is it possible to replicate the above formula from Excel into criteria in an Access Query.

Assume the cell reference $A2 is a text field which has customized date info in it but is not a recognized date format.

View 7 Replies View Related

Tables :: Replicate NETWORK DAYS Formula In Excel

Jul 29, 2015

Is there a way to replicate NETWORKDAYS formula in Excel to Ms Access?

View 2 Replies View Related

Access Formula Equivalent To Index Match In Excel?

Sep 30, 2012

PRODUCT
VALUE A
VALUE B

WW
10
100

XX
20
200

YY
30
300

ZZ
40
400

I have above table in access. I want to use this table in query to find value of any product using a formula (Value A*X)+Value B. The product and X query picks up from another table. The same can be done easily in Excel using Index, Match formula but how in Access.

View 1 Replies View Related

Converting Excel Check Digit Formula To Access

Nov 5, 2014

I am calculating the check digit for a 13 digit number in Excel and I need to be able to do it in Access.

My Excel formula; =1&E1&MOD(-SUM(MID(1&E1&0,{1,3,5,7,9,11,13;2,4,6,8,10,12,14}, 1)*{3;1}),10) -- cell E1 holds the base 12 digit number.
My attempt to convert to Access; =1 & [txtBase] & [-SUM(MID("1" & [txtBase] &0,{1,3,5,7,9,11,13;2,4,6,8,10,12,14},1)*{3;1}) mod 10] -- txtBase holds the base 12 digit number

View 7 Replies View Related

Date Difference Query Based On Excel Formula

Aug 24, 2015

i want to write a ms access query for date difference.i have the excel formula for that.in attachment i have shown wot i really want as output.i want to make a IIf query for these conditions.

in excel i did this like:
=IF(J13="",IF(INT(B13)=INT(AE13),C13-DAY(I13)+1,C13),IF(INT(AA13)=INT(B13),IF(MONTH(J13 )=MONTH(I13),J13-I13+1,DAY(J13)),IF(INT(AA13)=INT(D13),DAY(J13)-E13,0)))

how to do it in access query.

View 4 Replies View Related

Forms :: Convert Excel Formula Into Expression Builder - Invalid Syntax

Mar 22, 2013

I am trying to convert an excel formula into the expression builder on my form.

the excel formula is this:
IF(AND(E4>=2.65,F4<1.85),"Poor",IF(AND(E4<2.65,F4< 1.85),"Unacceptable",IF(AND(E4>=2.65,F4>=1.85,F4<2 .65),"Generally",IF(AND(E4<2.65,E4>=1.85,F4<2.65,F 4>=1.85),"Poor",IF(AND(E4<1.85,F4<2.65,F4>=1.85)," Unacceptable",IF(AND(E4>=3.45,F4<3.45,F4>=2.65),"H igh",IF(AND(E4>=2.65,E4<3.45,F4<3.45,F4>=2.65),

[Code] ....

In the expression builder I put this:

= IF(AND( [Objective Total] >=2.65, [KSF Totals] <1.85),"Poor",IF(AND( [Objective Total] <2.65, [KSF Totals] <1.85),"Unacceptable",IF(AND( [Objective Total] >=2.65, [KSF Totals] >=1.85, [KSF Totals] <2.65),"Generally",IF(AND( [Objective Total] <2.65, [Objective Total] >=1.85, [KSF Totals] <2.65, [KSF Totals] >=1.85),"Poor",

[Code] ....

It gives me an error 'expression you entered contains invalid syntax, you may have entered a comma without a preceding value.'

View 2 Replies View Related

Convert Formula Into Formula Result

Apr 6, 2007

Hi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks

View 7 Replies View Related

Using Query Formula Information To Create Another Formula In A Seperate Query

Nov 20, 2006

I have a query that calculates input information into a value that then needs to be compared to another query values and will be used to output a % change in a third query. Is there any way to make this happen? Thank you in advance!

View 2 Replies View Related

Formula Help

Apr 28, 2005

i have a list of check boxes which state whether a ticket has been sold or not. what formula can i use to find the percentage of tickets that have been sold (by counting the number of checked boxes). also how can i implement that onto a report to display it next to a pie chart of the information. if it cannot be put on a report how can i put it onto a form?

thanks in advance

View 6 Replies View Related

Formula Help

May 23, 2005

I have a nice database which is working well apart from one part

I have 2 date fields, you enter in any date in the first and i need a formaula which will generate a date 6 weeks in the future

can anybody help or suggest what to do?

View 1 Replies View Related

Formula

Apr 16, 2007

Hi

I am trying to create a formula that will update a field in my table - I have created an update query and want to update the field GPA.

I have tried using the iif formula without success. Here is what I want to calculate.

I have two fields one is Score the other is Baseline. The formula needs to place the Score of 4.0, 3.5, 3.0, 2.5, 2.0, or 1.5 in the GPA field given the following.

If the difference between the field score and baseline is .00 then the score 4.0 goes in the GPA field, if the difference between the field score and baseline is .05 then the score is 3.5 in the GPA field, if the difference between the fields score and baseline is 1.0 then the GPA is 3.0....

If both fields are equal then score is 4.0
.05 score is 3.5
1.0 score is 3.0
1.5 score is 2.5
2.0 score is 2.0
2.5 score is 1.5

Can anyone help me with this formula.

View 4 Replies View Related

Sum Formula?

Apr 6, 2006

I have a customers table, products table, and a transaction table that brings them together fields in tblTransaction:

Transaction ID
Order Number
Customer ID
Title ID
Date
Status

I want to create a query so that you can type a name of a customer in, and it will bring up all the items they have bought, and will add up all the prices (from tblTitle) and show it at the bottom but im not sure how to get this calculated field into the query? Whats the sum formula that you put into a query?

View 12 Replies View Related

Formula...

Nov 7, 2006

I have a form that people fill in where they can choose from 4 boxes (and they can tick as many as they want - it is a yes/no box)

I have tried to write a formula for a queryto show where none of the boxes have been ticked.....this doesn't work:

=(IIf(IsNull([Q3(i)]) And iif(IsNull([Q3(ii)]) And iif (IsNull([Q3(iii)]) And iif(IsNull([Q3(iv)]),"1","0"))

???? what should I write?

View 3 Replies View Related

Formula

Nov 4, 2004

Because I am stupid at math, can some please help me out with the formula to get the percent of something that has changed.

Say I have October usage of 2685 and November usage of 6294. I need to know what the percentage of the change is from Oct to Nov Basically the formula to make this calculation.. The calculation will be done in Excel. I know this is an access help area, but you guys have really helped me in the past.

View 2 Replies View Related

Formula

Mar 21, 2008

I need a formula that will give me the count of the number of files I scored 3.0, 2.5,2.0,1.5, and 1.0. These scores are in one field called Claim Quality Index.

What I am trying to do in a query is to count the number of files I scored a 3.0 and in the next column of the query give me the count of the number of files I scored a 2.5 - same for score 2.0, 1.5, and 1.0.

I have chosen the count in the query but I cannot get the criteria to only give me the above. I believe I need to use the expression and name the field CQI3: ????(formula that I cannot get right.

Can anyone help - I have tried various ver. of the IIf formula but to no avail.

View 10 Replies View Related

Sum Formula

Jul 6, 2006

I have the following idea in my mind. The only problem is that I have no clue how to make this work.

[IMAGE]

Anynone know how to make this happen?

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved