Solved

Not understanding how a excel fromula caldulates

Posted on 2013-01-04
9
183 Views
Last Modified: 2013-01-14
I have a excel formula that I need to transfer to a access formula but i dont understand the formula what it means or how it is calculating.

equavalent vehicle complete is the  Name of the field that it is calculating and this is the formula

=SUMIF($D$31:$D$395,"="&D4,$AE$31:$AE$395)


column d31- d395 are name of units like FY9 or fy10


column ae31-ae395 are percentages some are 100% SOME ARE 10 % and 5% AND SO ON
0
Comment
Question by:gigifarrow
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 16

Assisted Solution

by:terencino
terencino earned 250 total points
ID: 38746038
The equivalent formula in Access is DSUM, have a look at this tutorial and let us know if you need any further help with that
MS Access: DSum Function

Essentially you must convert the Excel sheets and ranges to tables and fields, but otherwise the concept is exactly the same
...Terry
0
 
LVL 10

Assisted Solution

by:honestman31
honestman31 earned 125 total points
ID: 38746042
source  http://office.microsoft.com/en-sg/excel-help/sumif-HP005209292.aspx

SUMIF   Adds the cells specified by a given criteria.
0
 
LVL 16

Accepted Solution

by:
terencino earned 250 total points
ID: 38746056
So in Excel the SUMIF looks up in column D, picks out the cells that are the same as the criteria, eg FY10, then adds up the matching percentages on the same rows in Column AE.

Similarly say in Access you have a table called tblPercentages, it will have one field Year with data same as column D, and another field Percent with all the values from Column D. Then to get the same value as the SUMIF, your DSUM formula  would be:

=DSum("Percent", "tblPercentages", "Year = FY10")
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:gigifarrow
ID: 38747021
Okay thanks experts but when i total them up it is not coming out correct. here is a example.The field is called Vehicle Equavalent I have it highlighted.

And the fields that are  supose to total i have them highlihted also.
exampleofvehicleequivalent.xlsx
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38747407
I don't see any formulas in that sheet - can you give a specific formula that you believe is mis-calculating?

If you are using the formula from your question, i.e.

=SUMIF($D$31:$D$395,"="&D4,$AE$31:$AE$395)

then because D4 is blank that will sum all the AE column values where the corresponding cell in column D is blank

regards, barry
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
ID: 38751091
Yes, please post a sample sheet that actually works for the formula you posted.

Then post a sample of the Access data and the exact results you want...

As was stated by terencino, the equivalent in Access would be Dsum.
The basic syntax is:
=Dsum("YourField", "YourTable", "YourCriteria")

For example:
=Dsum("Salary","tblSalaries","EmployeeID=" & me.txtEmployeeID)
This will sum all the salaries if the EmployeeID matches what is in the EmployeeID textbox
(Presumes EmployeeID is a number)

If you are dealing with a string criteria (like FY10), then the syntax becomes "roughly" something like this:
=Dsum("Cost","tblCosts","Unit=" & "'" & me.txtUnit & "'")


JeffCoachman
0
 

Author Comment

by:gigifarrow
ID: 38763105
Sorry about that. Been busy I had copied and pasted it and I didnt know that the formulas would not show up here is the formulas.

The field is called vehicle:equavalent vehicle complete
BUSK-III-FS3--STATUS-REPORTforhe.zip
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38763605
So then the info we posted should help you build your equivalent Dsum() formula
0
 
LVL 16

Expert Comment

by:terencino
ID: 38766148
Hi gigifarrow it looks like your SUMIF works fine, why do you think it is not working?
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now