Inclusing Calculated Cells In SUM

Aug 20, 2008

It is a few years since I worked in the IT industry and used EXCEL a lot.
I want to use (formula) SUM on a cell which 'sums' 3 other cells one of which has a the result of a formula but the result of that SUM is always 0 (zero). I am sure I can remember doing this in the past but cannot determine how it is done.
eg.

Cell Contents
C10 1000 (value)
C11 =A1*(A10/A20) (formula)
C12 2000 (value)
C13 =sum(C10:C12)

What do I need to do so that (cell) C13 results in the correct value instead of 0 (zero)

View 5 Replies


ADVERTISEMENT

Retain Leading Zeros In Calculated Cells

Sep 15, 2014

I had been trying to calculate cells that contain numbers with leading zeros.

I can't convert the cells to TEXT because Formulas don't work in TEXT Cells.

I cant use Custom Formatting because the rows may contain different number of leading zeros.

Also Is there a way that I can trim the numbers from Col A and put them in C?

Currently I don't like having to change the RIGHT(G2, 3) to RIGHT(G2, 4)

if the number is smaller than the number of char. option.

View 9 Replies View Related

VLOOKUP Inconsistently Returns Formula Calculated Cells

Nov 6, 2009

I'm having a real problem with the Vlookup formula in that I have used it in a spreadsheet to search, using approximate, rather than exact values, several tables where I have inserted raw data with no calculations and it works fine. But with one crucial series of cells where I am trying to return results from columns of fields that are the results of calculations, it sometimes returns the correct result and other times returns an empty field with seemingly no consistency at all.

I've been careful to make sure the data in the cells in the lookup column are in ascending order, as required. I've checked the formatting (all the lookup cells are numbers, no zeros with the thousands commas inserted, and all the return cells are currency with two zeros and the $sign) and the formatting is consistent.

Does anyone have a clue whether the calculations in the cells might be the problem or if it might be something else and, whatever the case, how I can workaround the problem?

I'm a relative novice with Excel. I'm using Version 2007 but I'm saving the file as an .xls.

View 9 Replies View Related

Ignoring Blank Cells/zero Calculated Values In Drop Down List

Oct 2, 2007

i have a list which is populated with data from another sheet.. if there is no data in the corresponding cell on the other sheet then the cell is blank.. i've tried using:

=OFFSET($Z$2,0,0,MATCH(REPT("z",255),$Z:$Z))

but it doesn't see the blank cells as blank i.e. no data in them as they contain formula's.. (even although there are no values populated) - can anyone offer any help in relation to this???

View 9 Replies View Related

If Then Statement With Calculated Cap?

Jun 6, 2013

What I need to calculate is:

If Quota 1 percentage => 90%, then Quota 2 percentage = sales / quota. If Quota 1 percentage < 90% then Quota 2 = sales / quota, with the maximum # being 200%.

If Quota 2 pecentage => 90%, then quota 1 percentage = sales / quota. If Quota 2 percentage < 90% then Quota 1 = sales /quota, with the maximum # being 200%

I was able to calculate this fine with one of the scenarios above, but not sure how to make it work with both at the same time.

Quota

Sales
Percentage

5000
4500
90%
Quota #1

7500
16000
213%
Quota #2

View 1 Replies View Related

Calculated Items

Jul 6, 2008

I have a pivot table with a calculated item. The problem is that it shows data for all rows EVEN when there is no data there. So the pivot table is a lot longer that it needs to be. How do I get the pivot table to show only rows with data in it? Also, all of the fields DO NOT have checks beside "Show Items With No Data".

View 9 Replies View Related

Cap Calculated Amount

Sep 19, 2007

I've written a custom function that calculates commissions (as a %) based on three variables.

I wanted to know what code I can use to cap the amount of commission paid out to a specific % amount. Something like "as soon as X exceeds 3 times the amount of Y, payout 3 times Y as the maximum".

View 4 Replies View Related

VBA Macro Using Calculated Value Out As New Input In

Jul 21, 2012

I am trying to write a VBA macro that will mimic what i have already done in an excel worksheet i have made. The excel worksheet uses inputs for a heat exchanger and finds the temperature as it leaves the heat exchanger. This temperature is then the new input for the second line of the worksheet and it changes quite a few of the values in the sheet. I have the excel worksheet working where you just drage the row down and it does it automaticly, but i would like to have a VBA macro that all the values can be input in with the number of cycles until the heat exchanger equalizes out.

I am able to get the macro to work when it calculates just the first cycle, all the equations work my variables are ok for just one time. What i don't know how to do is make my program realize that it has to use the calculated temperature out of the heat exchanger as my new input for the heat exchanger. I know how to do it once so it is more of a syntax or understanding of a loop or an array for the values that are changing.

View 4 Replies View Related

New Calculated Field In Powerpivot

Nov 14, 2013

I have a column in powerpivot that has Products total amounts. Some of them are negative amounts but I want them to be positive. How would I write an IF statement for it? I want it to say " If Division amount column is a negative, then multiply by -1 but it is a positive , leave it as a positive" .

View 2 Replies View Related

Lookup Value In Calculated Field

Jun 21, 2014

Take a look at the attached spreadsheet: Value_Lookup.xlsx

We have a pivot table with the "Product" on the Row context and a date ("FullDate") selected on the filter context.

The FactTable is related to the Calendar table by "FullDate" (which is marked as Date Column in Powerpivot)

In the Calendar Table, next to "FullDate" we have 2 more dates in the same row: "1 Month Back" and "1 Quarter Back".

We just need 2 calculated fields that return the "Price" value from the selected "Product" on the Calendar dates "1 Month Back" and "1 Quarter Back".

View 8 Replies View Related

How To Change A Calculated Cell To A Value

Sep 16, 2008

I am entering data for a month on one worksheet. The next month I change the date and reenter new data. I want another worksheet to record the data on a month to month basis, one month in one column, the other month in another column, to have year to date. Is there anyway to do this. Currently, I use an if statment =If(a1="January",b1), but I need to change these to values before I do the next month, and alter the formula to reflect the new month name)

View 11 Replies View Related

Placing A Calculated Value Into A Cell

Jan 7, 2010

if its possible to place a value (the product of a formula) into a cell ie:

Cell A1 = "a"
Cell A2 = "b"

I would like to be able to say: If A1="a" and A2="b", then place "Yes" into D1.

I realize that =if(and(a1="a",a2="b"),"Yes",0)

placed in D1 would give the same result, but I do not want to have a formula in D1, I simply want the characters "Yes" placed in there.

View 3 Replies View Related

Validation On A Calculated Cell

Jan 27, 2010

I am trying to input data validation on a calculated field. The calculated field (sum of 10 fields above it) must not total over 100%. I have used the following in the data validation menu: Allow: whole number. Data: less than or equal to 1

This doesnt seem to be working when I input values of a total greater to 100 from its input cells however, it does return an error if I manually input a number over 100%, then it gives me an error message. Edit - sample sheet attached.

View 4 Replies View Related

Rounding Up Calculated Value To Value Within List?

Nov 30, 2012

I have a list of figures (around 100 long) and I have a field which calculates a value somewhere within the range of the list (between the lowest and highest value in the list). I would like to round my calculated value UP to the closest value within the list.

For example

List : 1 3 5 7 8 9 11 13 15
Calculated value =9.4
Therefore the rounded up value I want to obtain is 11.

View 4 Replies View Related

Referencing Sum With Calculated Field

Dec 14, 2011

I have a pivot table that looks like this:

Code:

productname impressions cost CPM
product1 1,000,000 4523 4.7
product2 5,000 2400 48.45
product3 3,000,500 6,000 3

CPM is a calculated field that uses the formula =cost/(impressions/1000)

The problem is that the numbers in the CPM field are not, in practice, the results of that formula. They are averages (since it's a data field, I had to choose whether it was to be a sum, average, etc.). This means that, for example, the CPM 4.5 on the first row is not a calculation using the two numbers to its left. It is the average of the output of thousands of calculations done on the raw data, in which every line item with a productname of product1 had its CPM calculated, and then all those output CPMs were averaged together.

This is not what I want. I want a field that calculates based on what's already in the pivot table. So that the formula would be more like:

=(sum of cost)/(sum of impressions)/1000

View 2 Replies View Related

Display Calculated Field

Mar 4, 2012

I have a calculated field - if it is 0 I would like to now show the 0 - just a blank cell - can I do that?

View 1 Replies View Related

Message Box Calculated Field VBA

Oct 12, 2012

I have created a macro to run different imports on the system and I need a message box calculated at the en displaying the message "Write Total Charges to Import: xxxx".

The total is the Sum of the cells listed on Column B. I have defined my total and need to show the message, and after the user has writen down the total and clicks OK, the cell showing the total is deleted. how to make it work?

The syntax I am using is:

Range("B" & EndRow + 2) = "=SUM(B2:B" & EndRow & ")"
MsgBox "Write Total Charges to Import: "
Range("B" & EndRow + 2).Select
Selection.Delete

The calculation to display is the result of (Range("B" & EndRow + 2) = "=SUM(B2:B" & EndRow & ")") but when I add the formula next to "Write Total Changes to Import" does not work.

View 2 Replies View Related

CountIf With Calculated Range

Nov 22, 2013

#1
1
2
3
4
5
6
7
8

#2
Red
Yellow
Green
Blue
Violet

[Code] .....

I am trying to determine how many times a value occurs in each quartile in a data set, with the range of my quartiles changing for each row.

Above is a simplified sample of the data I am working with. What I would like to do is calculate the length of a quartile for each row, then determine whether "Red" is in the 1st, 2nd, 3rd, or 4th quartile

Right now I have two output tables. The first is a "Quartile" table, which for each row counts the number of values entered, divides by four, multiplies by the appropriate quartile and rounds down. For this I use the function

=rounddown(counta(range)/4*(quartile))
For Q1 in this dataset, that is =rounddown(counta($B2:$I2)/4*(1))

Then I have a table which calculates whether "Red" occurs in each quartile, with references to the cell values in my quartile table. My formula for Q1 here is

=countif(index(row#2,1,match(Q1 value,$row#1,0)):Index(row#2,1,match(Q1 value,$row#1,0)),"Red".

The actual formula (can be used if pasting table values into excel) is:

=countif(index($B2,$I2,1,match(Q1,$B$1:$I$1,0)):index($B2:$I2,1,match(Q1,$B$1:$I$1,0)),"Red")

Where Q1 equals my rounddown formula noted above, returning "1" in this case. For subsequent quartiles, I change my reference in the Match formula to start with the previous quartile +1, and end with the current quartile.

View 1 Replies View Related

Insert Calculated Rows

Mar 14, 2007

I need to insert 2 calculated rows in a data table for each state, one is Direct +2%, and Contractor +2%. Ideally, I would like to copy down the field name above. Every record in the table has the same 2 Categories (Direct & Contractor) and 2 calculations need to be applied. There are ~ 50,000 records so copy insert is not an option. Can a macro be applied to do this ?

Current
StateCategoryCount
New YorkDirect150
New YorkContractor75
BostonDirect200
BostonContractor125

Result
StateCategory Count
New YorkDirect 150
New YorkContractor 75
New YorkDirect +2%153
New YorkContractor +2%76.5
BostonDirect 200
BostonContractor 125
BostonDirect +2%204
BostonContractor +2%127.5

View 9 Replies View Related

Self Calculated For The Current Month

Apr 9, 2008

I am working on spreadsheet that calculates yearly totals. It is set up similar to this:

Jan Feb Mar Apr May ETC. TOTAL
Charge 123 123 123
Goal 223 223 223 223
Cash 111 111 111
% Coll. 50% 50% 50%


The "goal" column is Self calculated for the current month (I.E. it's April, that goal is calculated) The "Charge, Cash, and % Collections" columns won't be fill in for April until its over.
have it add Aprils "Goal" but not add "Mays" and so on... Doing this for the entire year.

View 9 Replies View Related

Time Difference To Be Calculated

Mar 12, 2009

Date OutTime OutDate InTime InTime Difference

01-03-099:0001-03-09NIL
02-03-09NIL02-03-09NIL
03-03-09NIL03-03-0910:002 day(s) 1 hours 00 mins
03-03-0911:1503-03-0918:000 day(s) 6 hours 45 mins
04-03-099:00NILNIL
05-03-09NILNILNIL
06-03-09NILNILNIL
07-03-09NIL07-03-0914:453 day(s) 5 hours 45 mi

In Column E in want the time difference to be calculated as shown above.

View 9 Replies View Related

Formatting With Calculated Values

Jun 23, 2009

Column 2 is a calculated value (a sum of several other columns). I cannot get the code to work. When I enter the same value in the field it works, but not when it is a calulated value.

I know there must be a simple error in here somewhere...


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Then
Select Case Target.Value
Case Is > 382: Target.Offset(0, 0).Interior.ColorIndex = 3: Target.Offset(0, 0).Font.ColorIndex = 2
Case Is > 315: Target.Offset(0, 0).Interior.ColorIndex = 30: Target.Offset(0, 0).Font.ColorIndex = 2

View 9 Replies View Related

Reference Calculated Cell Value

Nov 25, 2009

to copy the calculated value of a cell to another worksheet.

I tried =IF('Actual Hours'!D2="*"&"LR"&"*",'Actual Hours'!D2,"")

which when copied down would return values with LR in them, however D2 is a calculated cell (='RAS SENT 2'!L2) so it returns a blank, and I dont want to just reference this other cell instead.

Is there a way to return the value of a cell?

View 9 Replies View Related

Blank Cell When No Value Is Calculated

May 3, 2007

I am trying to blank out Column D if there are no furthercalculations required. As you can see now by the example that I have attached the formula continues the calculation.

View 2 Replies View Related

Calculated Field In Pivot Table?

Jan 21, 2014

Can I add a calculated field formulas in Excel pivot table. Such as CONCATENATE?

View 2 Replies View Related

Pivot Table Calculated Field

Mar 26, 2014

Ihave a pivot table that takes data from a table, groups the duplicate items and adds their values up - so it shows just one entry for each item. I now want to add a calculated field in there so that I can divide one of the fields into another.

View 14 Replies View Related

Change Text Colour If Value Is Calculated By SUM?

Jun 12, 2014

I need to see easily if my cell is giving a value by using a SUM. This is so I can see if the SUM has been taken off or overridden by typing in a number.

View 4 Replies View Related

Transpose Calculated Value From Horizontal To Vertical?

Sep 4, 2013

I have a series of cash flows as follows:

Year 1 Year 2
Income 100 150
Expense 20 30
Net Income 80 120

I need to take the calculated "Net Income values" and also place them vertically by themselves, like this:

Year 1 80
Year 2 120

I know I can copy and paste the values easily using the "paste special... transpose", but how can I easily do this so that it continues to reference the original values? So that if I change the Income in year 1 of 100 to 120 the vertical Year 1 value would change from 80 to 100?

View 4 Replies View Related

Calculated Item In Pivot Table

Mar 4, 2014

I'm trying to insert a calculated row in a pivot table. I keep getting the error message 'Pivot table items can only refer to items in the same field as the calculated item'

Below is the format of my data:

Region(All)
Sub Division(All)
Sub Cluster(All)
Cluster(All)
A/C Level 3(All)
A/C Level 1(All)
A/C Level 4(All)
A/C Level 5(All)
Division(All)

Values
A/C Level 2Sum of FY11Sum of FY12Sum of FY13
Net Revenues 100 200 300
Op Exp 50 40 30
Calculated Field50 160 270

View 5 Replies View Related

Add Calculated Item To Pivot Table

Jun 21, 2009

Attached is a worksheet example, of my beauty salon with 5 therapists.

The input sheet - is the shhet into which I input data on a daily basis.

Weekly inc - This sheet shows the total that each therapist made in apivot table based on the input. The week numbers are 1 to 52 (or 53) depending on each financial year (07/08, 08/09 etc).

What I want to do is either have another pivot table or to add an calculated item to the weekly inc pivot table showing me how much commission each therapist took each week? This is to be done automatically by excel.

This commission is calcluated by using the target comm figures - E.G. Leanne's target is £480 - so she has to earn £480 a week before getting a commission. So if in a week she earns £500 - she will get a commission on £20 (£500 - £480). The commission is 10%, so she will earn £2 that week.

View 14 Replies View Related







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