validation for a sum that must = 100%In Excell 2002 I have a worksheet where the user can
choose what percent of the time three different procedures
will be followed. The total of the three choices MUST
equal 100.
Is there a way to get the data validation (or some other
non-VBA method) to tell the user his math doesn't add up
to, or goes over, 100?
i.e., using cells 1A, 1B, and 1C as entry cells, cell 1D
must = 100 (using the formula: "=1A+1B+1C" in cell 1D)
Thanks,
JR
JR,
I would use conditional formatting.
Select D1.
Goto menu Format>Conditional Formatting
Set the test to 'Not equal to', th...
sum totalHello,
I have al total in column a ($3.95) and a total in column b 4,803. When I
try to divide it the total comes out as ($18,987.28) but it should be
($18,971.85). What am I doing wrong. Thank you for any help you can give
me.
--
thank you mac
See
http://www.mcgimpsey.com/excel/pennyoff.html
My guess is that your "3.95" is actually more like 3.9532
In article <2909D013-4352-4235-A904-29D66826CCFA@microsoft.com>,
mac <mac@discussions.microsoft.com> wrote:
> Hello,
> I have al total in column a ($3.95) and a total in column b 4,803. When I
> ...
Pivot Table Sum Columns and RowsI am doing a Pivot Table importing data from Access and am trying to get sums
for both columns and rows. I can get one or the other but not both. I tried
adding a calculated field and calculated item and they do not show up, even
though I have entered a formula. If I delete the total columns and rows,
then it lets me add a calculated field or item. Any ideas?
You do not mention if you checked up under pivot table Options
Grand Totals for Columns
Grand Totals for Row
--
Message posted from http://www.ExcelForum.com
...
Summing negative time
Hi All,
I am having problems adding time values hwen a negative time is
involved. I have read all the posts on this and have chosen the 1904
date option, but no success.
I determine the difference between times and then subtract a standard
day from this amount to achieve a result (positive or negative). Then
I want to sum the results. Works fine as long as I don't have any
negative results. If I do have negative then the answer is always 0.
Example:
Morning.............Afternoon..............Balance
In.......Out........In........Out..........Total..Flex...Balance
8:15...
crash on conditional sum wizardHi,
I can think of no reason why Excel (2002) suddenly has decided to exit (even
wihtout asking to save te current workbooks!) every time I ry to use the
conditional sum wizard.
I have tried to uninstall he add-ins, then run excel, op en workbook, close
it and exit excel to make sure it runs without add-ins.
In a next session I reinstalled the add-ins. But when I click on conditional
sum wizard excel exits.
Can anybody tell me what is happening here?
greets
Sybolt
--
||//////||
( o o )
( O )
-
( )
( )
(_...
Sum total bolded figures in a column
I am trying to come up with a simple cell formula to total or sum
amounts bolded in a column.
Any Ideas?
Thanks from a person needing help.
mikeburg
--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581
View this thread: http://www.excelforum.com/showthread.php?threadid=392229
There's nothing built into excel that will sum values based on formatting.
You could use a User defined function, though.
Option Explicit
Function SumBold(rng As Range)
Applicati...
Sum if Condition is Equal in Range Date and find columnI want to make a sum if Range is a week number and if style is Equal to
CONC-92 or CONC-45
Week# 49 Week# 50
CONC-92= 27 CONC-92= 30
CONC-45= 27 CONC-45= 30
Datas are in a pivot table and...
Pivot table looks like this:
Date CONC-92 CONC-45 CONC-92 CONC-45
12/7 5 5 10 10
12/8 2 2 10 10
12/9 5 5 10 10
12/10 5 5 10 10
please help
--
Lorenzo Díaz
Cad Technician
...
Sum using probably a VLOOKUPHi,
I'm looking for a way to make a sum of values in a column depending on
the respective value of another column.
Example:
A1 = 1
A2 = 2
A3 = 1
B1 = 100
B2 = 300
B3 = 125
I'd like to be able to make a sum of B values when A values = 1. In that
case, my sum would be 225.
Thanks,
Alex
One way:
=SUMIF(A:A,1,B:B)
In article <OQ#LYB6hEHA.356@tk2msftngp13.phx.gbl>,
"Alex Langlois" <alex.nospam@nospam.net> wrote:
> Hi,
> I'm looking for a way to make a sum of values in a column depending on
> the respective value of another column.
...
INDEX and sumI have a table of products in rows against years in columns with revenues in
the data array. I want to sum revenues for a 'x' year period starting at 'y'
year for product 'p' on a separate worksheet. So it's a 2D lookup.
Following useful tips from the community I was able to find the revenue for
'y' year for product 'p', using INDEX:
=INDEX('new product sales'!$A$1:$P$603,MATCH($C14,'new product
sales'!$A$1:$A$603,0),MATCH($Q$2,'new product sales'!$A$1:$P$1,0))
- where $C14 gives the product name and $Q$2 shows ...
look up with multiplication and sumHi, could anybody help me? I have a worksheet with expenses i
different currrency, I wanted to transform everything in dollar an
then add then. In column A I have the expense, in column B I have th
currency. Then I have a table on the side with the exchange rates.
Column C currency again and Column D the exchange rate. Is there a wa
to create a formula where I transform all expenses in dollar and the
add them?
Thanks,
Marco
--
Message posted from http://www.ExcelForum.com
Hi
why not format the cells conatining the values as currency?. And then
simply use SUM on this range
--
Regards
...
How do I sum multiple fields relating to a single field in a formI am trying to set up a form that will totalise multiple fields relating to a
unique identifying field in the same table
Hi,
put an unbound textbox on the form.
Set its datasource to something like = Nz(Me.txtA, 0) + Nz(Me.txtB,0) +
Nz(Me.txtC,0)
where txtA, txtB, txtC are controls for the fields you want to sum.
Jeanette Cunningham
"Gezza77" <Gezza77@discussions.microsoft.com> wrote in message
news:03C166B7-5710-4EB2-B744-4635F8633B6C@microsoft.com...
>I am trying to set up a form that will totalise multiple fields relating to
>a
> unique identifying field in t...
Highlighted Cell group provides a Count instead of a Sum in bottom rightSometimes when I download a file into excel it doesn't allow me to ad
the group. Additionally, when highlighting the group when it i
supposed to give a SUM in the bottom right (off the worksheet), i
shows a count. I've verified the cells are formatted as numbers. I
for instance I overwrite the existing amount with the same number i
becomes addable. I would like to be able to toggle between these tw
abilities. Thanks.
MACR
--
Message posted from http://www.ExcelForum.com
Hi
the numbers are probably stroed as 'Text'. Try the following:
- select an empty cell and copy thi...
Sum across spreadsheets & ignore blanks to get rid of #VALUE! erroHi,
I'm trying to sum the total of the same cell in six adjacent spreadsheets
(named 'July' to 'December') onto a separate summary sheet at the end. My
problem is that many of the summed cells are blank which means my formula
gives me a #VALUE! error. Current formula is:
=SUMIF(July:December!A108,"<>""")
After trawling the forum for the answers to similar problems I came up with
the formula above (also tried several variations) but it still returns the
#VALUE! error when one or more of the summed cells is blank.
In addition, if all of the s...
count or sumhow can I count all the occupied cells in the same column
if the cells contain text and numbers?
A
row 1 br1
row 2
row 3
row 4 cb2
row 5
row 6
row 7 df6
ect down to row 200.
the answer should be 3 (the number of cells occupied)
please help thanks
rgs jerie
use:
=COUNTA(A:A)
Regards
Trevor
"jerie" <anonymous@discussions.microsoft.com> wrote in message
news:4a1c01c5214e$19ffd710$a401280a@phx.gbl...
> how can I count all the occupied cells in the same column
> if the cells contain text and numbers?
> ...
SUM Function not UpdatingHello,
One of our business partners is having trouble w/an Excel document. They
have the SUM function used multiple times throughout the document, and as
they have changed values in certain fields, the SUM has automatically updated
itself to reflect this. It's not working anymore. I have tried to redo the
formulas but they simply won't work. The SUM stays the same regardless of
the values in the cells. I can't even create a brand new column w/the SUM
function at the bottom...it won't up date either.
In a new document I can get the SUM function to work properly, how...
sum a cell value over 75Please help!!
Excel beginner! I need to build a formula that will add any remaining value
over 75 in a cell.
For example:
In cell A1 = 76.5
In cell A2 - I need a formula that will sum anything over 75 and enter 1.5
into cell A2.
Thanks in advance for everyone's help!!
> For example:
> In cell A1 = 76.5
> In cell A2 - I need a formula that will sum anything over 75 and enter 1.5
> into cell A2.
Perhaps, as a start .. try in A2: =IF(A1>75,1.5,"")
A2 will return 1.5 if A1 contains a number exceeding 75, otherwise A2 will
just appear empty ("") -- ...
Column Sum in FooterHi,
I have created a Tabular report that lists names and weights for different
people. I want to create a stand-alone field in the page footer that will
display the SUM of all the weights on the report. Can somebody please help
me? (I'm using Access 2000 btw)
I have tried creating a text box with the Control Source property set as
"=Sum(weight)". However, this just produces an error.
Thanks in advance.
Julie Smith wrote:
>Hi,
>I have created a Tabular report that lists names and weights for different
>people. I want to create a stand-alone field in the page f...
how do I default sum to 0 for negative numbers in excel
One way"
If(SUM(A1:A10)<0,0,SUM(A1:A10))
tj
"ej764" wrote:
>
Try
=MAX(0,SUM(F2:F300))
--
Don Guillett
SalesAid Software
donaldb@281.com
"ej764" <ej764@discussions.microsoft.com> wrote in message
news:5FBED33E-FC8F-4E0B-9E1B-AB63205D589E@microsoft.com...
>
Hi
=SUMPRODUCT(--(A1:A10>0),A1:A10)
Arvi Laanemets
"ej764" <ej764@discussions.microsoft.com> wrote in message
news:5FBED33E-FC8F-4E0B-9E1B-AB63205D589E@microsoft.com...
>
...
Sum of the Largest ValuesHi.
I have a gradebook. I have 16 assignments, and thus have
16 columns for scores. However, I would like to include
ONLY the highest 10 scores in the final grade. In other
words, how can I find the sum of the largest 10 values in
a 16-cell range?
Thanks!
=SUM(LARGE($A$1:$P$1,ROW(INDIRECT("1:10"))))
arary entered using CTRL+SHIFT+ENTER
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03
----------------------------------------------------------------------------
Best wishes to all, and hope f...
Conditional Summing across ranges using arraysHi all -
I'm struggling to find a formula that will solve my problem:
I have a matrix of numbers defined by a series of numerical column and
row headers.
RefCel 1 2 3 4 5 . . . m
1 a # # # #
2 # # # # #
..
5 b # # # #
..
n # # # # #
What I'm trying to do is for a give column header and a subset of row
headers,
sum the intersecting values. Let's assume that the subset are held in
a named range Subset and the row and column headers are in range
called RowHead and ColHead and...
Excel sumsWhy do totals that are grearter than 100 get ####
How can that be changed
Try and widen the column and see if that helps
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
"2141233" <2141233@discussions.microsoft.com> wrote in message
news:36F6768A-47E4-474B-8557-9A3719E5FB4D@microsoft.com...
> Why do totals that are grearter than 100 get ####
> How can that be changed
Because the cell width is too small
Widen i...
Look up column name, match rows (a/cnumber) & summing up....its confusing!Hi All,
Here is what I'v been trying to do but..!
I'v 2 sets of data (data1 & data2) Column name may be sane/different in
each data set and same applies to acct_no.. I want to prepare a report
that combine product & accounts data show accumulatd result on another
sheet/file.
Data 1 - upto Jan 2006
Acct. No Total Prod - A Prod - B Prod-C
1001 51 10 30 11
1002 47 15 20 12
1003 80 20 15 45
1004 64 25 16 23
Total 242 70 81 91
Data 2 - Feb 2006
Acct. No Total Prod - A Prod - B
1002 7 5 2
1004 16 10 6
1009 9 3 6
Total 32 18 14
Report required
Acct upto Jan'06 Prod - ...
Summing instances of text across worksheetsHello, I have a workbook with separate sheets for each month. The sheets
contain a list client names (entered exactly the same on each sheet). I need
to summarize how many times a client name appears in total. For example, John
Brown may appear Jan & Feb (=2) where Mary Brown may appear Jul, Sep, Dec
(=3).
I've been trying pivot tables without luck. Hoping someone can help.
Thanks in advance!
Never mind, I consolidated all the worksheet data into one and the pivot
table works fine.
Thanks anwyay!
"George" wrote:
> Hello, I have a workbook with separate sheets fo...
BETWEEN DATES SUMHi,
Col A has dates in order
Col B has amounts
Cell D1 is the beginning date
Cell E1 is the end date
I want to sum Col B where the dates in ColA are between the dates in D1 and
E1.
Thanks for your help.
Jerry
I believe this should do it:
=SUMIF(A:A,">="&D1,B:B) - SUMIF(A:A,">"&E1,B:B)
tl
"Jerry Kinder" wrote:
> Hi,
>
> Col A has dates in order
> Col B has amounts
>
> Cell D1 is the beginning date
> Cell E1 is the end date
>
> I want to sum Col B where the dates in ColA are between the dates in D1 and
> ...
Sum Days into WeeksI would like to save myself a whole lot of work please...
I have two spreadsheets, one has all the days of the year across the
columns, and the next has all the week ending dates across the colums.
What I need to do is sum the data in each of the rows below the daily dates
into weekly chunks on the same rows in the Weekly spreadsheet.
I have also added these up into montly chunks, but there was only 12 sums to
do so wasn't too bad. I don't relish having to do 52 of them.
Any assistance would be appreciated.
If you have *all* the dates for a year across a row then you mus...