Solved

How do I calculate and accumulate values in an Excel row depending on the values ?

Posted on 2013-01-24
2
453 Views
Last Modified: 2013-01-24
Hi Experts,

I have an Excel 2010 worksheet which calculates the total expected annual rates of pay for  contractors.

Against the name of a contractor is :

a) a daily rate of pay in £
b) month columns from Jan to Dec with either a value of 1 (contractor expected to work) or 0 (contractor expected not to work)
c) the average number of days per month (ANDM) a contractor is expected to work - 21.7 in this case.
d) the total amount of pay expected to be earned by the contractor for the whole year, calculated by multiplying each month (which has a value of 1) by the ANDM multiplied by the daily rate of pay.

I'm struggling to come up with a solution for item d).

I guess the answer is an array formula but I'm not sure how to accumulate the monthly amounts based on whether they are expected to work.

I've attached an example file.

Thanks
Toco
Annual-Rates.xlsx
0
Comment
Question by:Tocogroup
2 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 38815409
May be:
=SUM(C4:N4)*O4*B4
add it t first string and extend down.
Annual-Rates.xlsx
0
 

Author Closing Comment

by:Tocogroup
ID: 38815825
Nice solution.
Many thanks
Toco
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

756 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