look at my attachment and see what I am doing wrong in my formula? I have a hard time understanding the Sumproduct formula and when to use comma's, double negatives, addition, etc.
View 2 Replies
I need to C8 - C19 only to add up jobs won by andrew (in current orders). It needs to be month specific. what i mean by that is I need the formula to do what its doing now (adding up the jobs by and putting the totals into the according cell depending on what month they were won.
View 2 Replies View RelatedI have Names in column A, Data in Column B. Example
A1 John B1 1000 C1 5:32:05
A2 Jim B2 500 C2 5:56:55
A3 John B3 600 C3 6:45:65
A4 Bill B4 300 C3 7:21:05
In another column I have the names of all the possible people that I will need data from and next to them I will need a formula to tabulate all their totals from column B and then another formula that will skip B and total column C's total.. I have a formula that I used from awhile ago when I needed to offset the data but I can't figure out how to just take the data to the right of it and then another formula to skip column B. Here is my old formula =SUMPRODUCT(($A$1:$A$291=G14)+0,OFFSET($B$1:$B$291,1,0)+0)
i'm a complete excel novice, but i think this should be pretty easy for you guys...
i just bought a new satnav system after 4 years with tomtom...
anyway, to get the speed camera database to work with it, i must add a couple of commas at the end of each set of co-ordinates contained within the spreadsheet, like this:
-3.23956,51.52737,1,50,1,67,,
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247,,
thing is, most of them don't have the 2 commas at the end and just end with the number, like this:
-3.23956,51.52737,1,50,1,67
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247
therefore, what i need to do is make up a formula in excel to search for a double comma at the end of each set of co-ordinates and if it doesn't already have them, then it should insert them...
i'm guessing for anyone with excel experience, this should be pretty easy to solve...
I have a question regarding calculating percentage differences, here is how our formula works, but I've done a little research regarding using negative number for percentage calculations and am not sure...
For sample:
this year (TY) we had net profit of $200 million
last year (LY) we had net loss of -$32 million
our formula works kinda like this:
=IF(B49="","-",IF(B49=0,0,D49/B49-1))
but if, the comparing numbers is negative should I convert that to an absolute number using ABS ? what if both numbers are negative ?
The way I understand the current formula is like this:
if B49 is blank, then insert a dash, otherwise IF B49 is 0, then 0 (since we can't divide by 0), otherwise do the formula.
i'm looking for a formula that gives the greatest number of a range
problem is that there are positive and negative numbers in the range
but I only want to look at the value (+/- signs are not important)
my table is something like below:
my program sheet looks something like this:
I need a double condition formula to check the date, if matches the date Column (A), and matches product Column (b), then returns Column (C) which is the doc#.
I Could easily do it if it is a sum product formula, but in this case, it is alpha numberic.
date: 14 Jun (dd / mm)
product -- Doc#
apple ---- ac21 (so what formula do I put here?)
Orange --- abc2 (formula?)
date --- product --- Doc#
12 Jun -- apple ----- abc1
12 Jun -- Orange --- ab12
14 JUn -- Apple ----- ab21
14 Jun -- Orange ---- abc2
I use sumproduct to put total qty into the Defect Table. But it take a long time for excel to run the counting process. Problem: From master data sheet, I want to plot the qty into the defect table follow by date occurring and by section
View 2 Replies View RelatedThis task joins a string together based on a number of characters per cell in the range.
I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
I am a new excel user. I a trying to write a certain formula but am having trouble. I want to write the formula to add a column of numbers, say H-10 through H-15. Each cell will have a number in it, but I want only to add the cells if the cell precedding it in the G-10 through G-15 Collumn is blank. For example if cells G-12 and G-14 have an "X" in them, then I do not want Cells H-12 and H-14 to be added. I only want the formula to add cells H-10,H-11,H-13, and H-15. I used just 6 cells for example, the column of cells to be added will be a lot longer.
View 3 Replies View RelatedI wish to sum specific rows in a spreadsheet as a Grand Total. I eventually end up with a string variable called "GrandTot" that holds "=C3+C13+C25+C31+C39+C50+C53"
This formula is correct, but when I insert it into the spreadsheet it totals zero. Only when I manually write the formula does it recognize the cells it supposed to add. I am very confused as to why this is happening.
On a possibly related note, when I highlight a column in the spreadsheet, the count is including blank cells.
Lets say I have a #2 in cell A1 and #4 in cell B1 and would like an output in C1 as "plus 2".
Right now I have =if(a1>b1,b1-a1,???) what can I put in the [value_if_false] to return "plus 2" or do I have to use another formula?
I would like to know the calculator formula for addition via columns.
Eg 1. If i were to place 135 into Column A ;
12.95 into Column C ;
i would need to yield a result of 147.95
Eg 2. Place 189 into Column A ;
12.95 into Column C
i would need to yield result of 201.95 and so on. in the attachment is the sample file.
I am working on a spreadsheet that matches each cell in Column B (text) with the data (text) in a constant cell; if there is a match, the data that corresponds to the data in Column B (text) will average (Column G, number) using a CSA formula, for example: =AVERAGE(IF($B$3:$B$106=A$110,$G$3:$G$106))
Now the formula above works well, only I have to update the spreadsheet, so when I add new rows the $B$3:$B$106 and $G$3:$G$106 portions are useless.
Trying to use the INDIRECT function that many people successfully use in this forum, produces a #VALUE error,
=AVERAGE(IF(INDIRECT("$B$3:B"&ROW()-4)=A$111,(INDIRECT("$G$3:G"&ROW()-4))))
you guys very kindly helped me with a spreadsheet a couple of months ago, but i now need to adapt it for another dept. I have completed as much as I can.
I need column C and E in the 'totals tab' to only calculate contract and upgrade sales respectively (found in 'service orders' tab). I also need Scott's and ash's individual sales to be calculated in corrisponding tabs. Most of the formulas are in place so just need them tweaked slightley.
I'm looking for a formula that acts the same way as the subtotal formula (revises if rows are hidden and only adds visible rows). Is there anyway to get this removal of hidden rows but for subtraction?
View 3 Replies View Relatedmake a calculation(addition) and use the answer to multiply against another addition calculation....
The sum of (Monday!A1:A4) multiplied by the sum of (Monday!B1:B4) plus (Tuesday!A1:A4) multiplied by the sum of (Tuesday!B1:B4) and so on.
I get an "The formula you entered contains an error"-message when trying to format every other row with =MOD(ROW(),2)= 1 in the Conditional formatting dialogue. I know exactly how to do, and follow the instruction exactly.
View 9 Replies View RelatedI am using one file, in that out put is coming but some times it is showing out put as -1.
Please find the attachment.
In the Sheet 1 Column X some out puts are coming as -1, what is the error in my formula.
Platform Open tickets on 29.01.2014.xlsx
="autECLSession.autECLPS.SendKeys """&B4*100&""""
B4 is 3.00% and I need it to be 03 no %, 3.00%*100= 3 but I need anything less then 10 to be two digits still (1-9)
This would be the perfect end result:
autECLSession.autECLPS.SendKeys "03"
and for over 10
autECLSession.autECLPS.SendKeys "10"
the formula i used is " =IF(A2=A3;"YES";"NO")
the contents are as follows
A
Numbers
2525 - YES
2525 - NO
2526 - YES
2526 - NO
2528 - NO
2530 - NO
2527 - YES
2527 - YES
2527 - NO
how can i use a formula... that tells the last double entry yes too instead no?
for example above...
2525 - YES
2525 - NO (i want the formula say this "YES" too..)
So i want to find out through filtering.. all the double entries with YES.. so i can work with it without missing any double entries.
What formula do I use to double a number thats in another cell.
I need the formula to go into Column AC from the list of numbers that are in Column W
So for example if the number listed in W2 says 8 I need the column in AC2 to read 16 and when the next number down from that says in W3 -24 I need the column in AC3 to say -48 and so on.
I want to be able to drag the formula down the column (AC) so it picks up all the numbers in Column W3 and doubles each and every one of the numbers all the way down the page thousand of rows down.
So what ever number weather it's a plus or minus number I want it to double itself and have the number read out right next to it in the AC column to it's right.
I have a column with both positive and negative numbers, is there a macro or a bit of code that will convert all negative numbers in a selected column to zeroes?
View 3 Replies View RelatedI'm trying (with little success) to match some negatives and positives, but the formulas that I found on line keep on picking up all matches (just like a VLOOKUP formula)
23
23
-23
The formula would match the -23 with the 2 positive 23s. This formula goes in Column O
Long Version: This should be a fairly simple question, with a solution involving unchecking a box somewhere, but I can't find it. Whenever I enter a number in parentheses (for example: "(187.13)") into a cell Excel automatically converts that number to a negative--- "(187.13)" becomes "-187.13". I've been using the apostrophe option as a workaround (i.e., '(187.13)) to store it as text instead of a number, but this is becoming a hassle because it recognizes that (187.13) is a number and alerts me with an error message of numbers being stored as text.
Short Version: Basically, I want it to stop automatically making "(187.13)" become "-187.13" but I don't know where the setting is.
Power.jpg I must write a double interpolation formula for the table.
View 6 Replies View RelatedI am having trouble getting the double lookup formula to work with a table. See attached sample.
View 2 Replies View RelatedI've just seen a formula with two - (minus) signs right next to each other placed before a section in a formula while reading possible answers to my post. Here is the link: Understanding this formula. I've seen it before and am intrigued as to why it is there and what it actually does. Assume it is used such: = --(1+2). Would the answer then not be =--3 = 3?
View 4 Replies View RelatedI'm trying to SUM a column, but it has a negative number in it. I want to sum the column but ignore the negative number.
I.E.
5
46
12
-15
3
so the total would be 66. How is this possible or would i have to use another formula instead of SUM.
Let us say that this is a certain cash flow
Year - Cash flow
2007 - (200)
2008 - (100)
2009 - 50
2010 - 100
2011 - 200
2012 - (100)
2013 - (25)
2014 - 50
2015 - 300
For year 2007,2008,2012 and 2013, we have negative cash.
I want to compute for the investment cost, which I define to be the total accumulated cost before first positive cash. First positive cash occurs at 2009, so I want to sum 2007 and 2008, However, 2012 and 2013 also reports negative cash, so in the SUM formula, their values will be included.
How should I write the formula in order to sum all the negative cash before positive cash, disregarding all the other cash flows thereafter (be it positive or negative)?
