Help simplifying a SUMPRODUCT formulaThe following SUMPRODUCT formula produces the correct results but I'm
reasonably certain that there must be a more efficient way of constructing
the formula. There are basically two components to this formula separated by
the + sign. Each component performs the same calculation: the first part for
Class="MS" and the second part (after the +) for Class="MSTV". Isn't there a
way to construct the formula so that it would be calculated for Class="MS"
OR Class="MSTV" and eliminate the need for two steps? I tried to incorporate
the OR formula wi...
Help With an Excel FormulaI am in excel and I have the following formula = Q:26 typed into Cell
C:2. What I want to do is have a second cell D:2 that takes what ever
cell reference I enter into C:2 and adds 1 row to it. So cell D:2
would be equal to Q:27, which is 1 row down from row Q:26. Is there a
way to do this?
Not possible unless you use an add-in or user define function that reads the
text of the formula in C2
D McRitchie has UDF for that
http://www.mvps.org/dmcritchie/excel/formula.htm
then you could use
=OFFSET(INDIRECT(SUBSTITUTE(getformula(C2),"=","")),1,)
Regards,
Peo Sjobl...
How to vlookup and summing value?Hi, please help....
Worksheet name: "Pages"
Column A Column B
James $100
James $50
AJ $30
Light $46
James $80
When i type on cell A1 on a separate worksheet this formula
=VLOOKUP(A1,Pages!A2:B5,2,FALSE) , it only provide me $100. Is there a way to
sum up the total to be $230 under "James"?
Hi,
=SUMPRODUCT((A1:A15="James")*(B1:B15))
--
Mike
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
...
Formula help neededCan someone help me to shorten this formula, it slows down my computer so it
takes ages to recalculate the sheet when I enter something in it.
It is used to calculate time, basic formula (A2-A1+(A2<A1)) from C
Pearsson�s site
OFFSET(INDEX(Tid3;MATCH(A8&1;Feb!$A$9:$A$250&Feb!$B$9:$B$250;0);MATCH(Feb!$C
$8;Feb!$A$8:$D$8;0);1);1;0)-INDEX(Tid3;MATCH(A8&1;Feb!$A$9:$A$250&Feb!$B$9:$
B$250;0);MATCH(Feb!$C$8;Feb!$A$8:$D$8;0);1)+(OFFSET(MATCH(Tid3;PASSA(A8&1;Fe
b!$A$9:$A$250&Feb!$B$9:$B$250;0);MATCH(Feb!$C$8;Feb!$A$8:$D$8;0);1);1;0)<IND
EX(Tid3;MATCH(A8&1;Feb!$A$9:$A$...
Convert String to Numeric ValuesI have data string that I must convert to numeric values.
Ex:
00100200300400500600700800901000110015016
How do I get the above data string example to return the numeric values that
are three digit integers.
How do I get the number 16 for example?
Any assistance is appreciated
--
JenISCM
Try this:
Public Sub MySplit(txtIn As String)
' copy/paste to a standard module
' input from debug window:
' call mysplit("00100200300400500600700800901000110015016")
Dim i As Integer
Dim n As Integer
i = 1
For n = 1 To CInt(Len(txtIn) / 3)
Debug.Print n & " - " ...
getting value from a cell into another cell behind a text stringHi,
I'd like to know how to get a value from a cell into another behind a text
string by using a formula. For example,
A B
1 20 Result (20)
Thanks,
Xianbo
Use formatting
Pull-down Format > Cells... > Number > Custom and the the field enter:
"Result ("General")"
The advantage of using formatting is that the cell can still be used for
calculations
--
Gary''s Student
"Xianbo" wrote:
> Hi,
>
> I'd like to know how to get a value from a cell into another behind a text
> string by using a formula. Fo...
display order of values without sortingI would like to create a sheet that can give me a weighted score at the end
of the row.
What I mean by this is I have a sheet with 4 colums and 10 rows,
each of the rows will have a weighted number in each of the colums with a
total at the end.
With out sorting the rows I would like to rank them in importance, the
highest number being a 1 the next a 2 the next highest a 3 and so on. Is
there a function that does this or does someone have a formula?
Thanks
Try this…
This formula will create logic and will assign a value for the equivalent
word.
=IF(A1="two"...
FORMULAS please helpHi there
I am trying to create a formula that refences <> numbers in a columm then
adds up the from a different columm
i.e add b1:b100 only if a1:a100 is >10000 <12000 =46
a b
10000 32
9000 64
12001 86
11999 14
9990 12
One way
=SUMPRODUCT(--(A1:A100>10000),--(A1:A10000<12000),B1:B10000)
Regards,
Peo sjoblom
boogie wrote:
> Hi there
> I am trying to create a formula that refences <> numbers in a columm then
> adds up the from a different columm
> i.e add b1:b1...
Repeating formula in ExcelHi. I need to copy a number into 500 rows, with each number increasing by 1
in value. These numbers are used to track call numbers, so the format is
like this: 2005-001 and I need it to repeat itself up to 2005-0550, and I
don't want to spend the time typing each value in manually. Someone please
help!!!
One way:
Delete the dash from your number.
Select the cell with the number.
Go to Format | Cells | Number.
Choose Custom and type:
0000-000
click okay.
Then, (still with the same cell selected) go to Edit | Fill | Series.
Make sure the Step Value is 1.
Change to Columns and put a s...
Replacing formula to cells EXTREMELY slowHi!
I have the following code
------------------------------------------------------------------------------
Private Sub ToggleButton3_Click()
Dim i As Long
Dim Lastrow As Long
Application.ScreenUpdating = False
Lastrow = 1000
If ToggleButton3.Value = True Then
'Fill in the checking formula
For i = 0 To 5
Cells(4, i + 9).Formula
"=IF($A4="""","""",COUNTIF(INDEX('Check'!4:4,1,$AI$2):INDEX('Check'!4:4,1,$AI$3),
& i & "))"
Next
Else
For i = 0 To 5
Cells(4, i + 9).Formula
"=IF($A4="""",...
Excel chart data labels showing % change instead of value?I'm needing to have my excel chart show me the percent change in my 2 series.
I would like this in a data label over series 2 on the chart, but all I can
figure out to do is have the value labeled. Does any one have any
suggestions? I'm believe I'm using Excel 2002.
You can use Rob Bovey's XY Chart Labeler.
http://www.appspro.com/Utilities/ChartLabeler.htm
"MFritz" <MFritz@discussions.microsoft.com> wrote in message
news:29317BE2-4574-4623-8D5E-A3BA97B3FAE6@microsoft.com...
> I'm needing to have my excel chart show me the percent change in my 2...
Formulas dont work
I am trying to do a vlookup function with data that is imported and
calculated from another cell, and it shows a result of #na.
c3 is formated to custom "m" to show the number of the month from cell
b3.
b3 is "October-04" c3 shows "10" d3 "job name"
b100 "10"
a100 =vlookup(b100,c3:f20,2,false)
result is "#na"
If I enter "10" into cell c3 with format set to general, then the
result shows the correct answer.
Is there a way to correct this?
--
comotoman
-----------------------------------------------------------------...
Automatic Pareto data with FormulaI want to create a data table for use to create an Automatically updated
Pareto Chart, for this I need that the values automatically be sorted in
descending order, so I have the following example scenario:
Column A has Labels and Column B has Values
A -1
B -2
C - 3
D - 2
E -1
If I use the following formula:
=INDEX($A$1:$A$5,MATCH(LARGE($B$1:$B$5,2),$B$1:$B$5,0))
I get B, but there are two 2 and if I use the 3rd largest:
=INDEX($A$1:$A$5,MATCH(LARGE($B$1:$B$5,3),$B$1:$B$5,0))
I also Get B instead of the required D.
How can I get it so the end results looks like this:
C
...
Use input box to fill cells?My question may be quite simple, but I have not been able to find any
info on it (due, no doubt to ignorance)
I want to use the info from an input box to fill certain cells,
basically prompting the user for the info to populate the cells.
Let's say that I want to put customer names in A8-A100. How can this
be written to allow the user to have this info put in, be able to exit
at any point, and possibly insert cells if they go beyond the range
stated above?
Thanks
Maybe you could select the range, then do data|form?
David Sizemore wrote:
>
> My question may be quite simple, but ...
Display Formula ProblemAll,
I have search Microsoft's Knowledge Base, but could not find an answer.
Hopefully, someone here can solve my problem.
I am using Excel 2002. I have several project managers maintaining budgets
in Excel workbooks. I am creating a summary Excel workbook. To get the data
out of the workbooks, I start with entering an equal sign, click on the cell
in one of the other workbooks, and press enter. The value from the source
workbook is displayed correctly.
Since I want to capture several columns and rows around this first cell, I
want to copy formula in the first cell into near cells, I mu...
Returning a Value from Another WorksheetI have two worksheets in one Excel 2007 file that I'm working with, in which
I want the second worksheet to return values of the first worksheet. Hope I
can explain it clearly here:
The first sheet contains a list of data and numbers to caculate a points
system. For example:
Worksheet1
Name Column B Column C Points
Data1 100 5 4
Data2 75 2 1
...
2) In the second worksheet, I want to be able to input a specific value from
Column A (i.e., I inputted "Data2" from Workshee...
Setting a conditional value in a query to perform an additional quOk, I have a slight brain twister here and wondering if anyone could help
shed some light on this. :)
Im in the midst of creating a database that will hopefully replace a
paper-based system of storing 'shift runsheets' from a 24/7 office. There is
3 shifts per 24 hours; Morning (6am-2:30pm), Afternoon (2pm-10:30pm) and
Night (10pm to 6:30am). Each day it adds a new row of data to the table
"shift" and users can peform searches on previous sheets by searching the
table "previous" which links to "shift". When a user wishes to search, they
select the ...
percentage formula #2What is the formula for using one cell to figure a
percentage of another cell. Example:
colume A colume B
20000 13% of colume A
Thanks
One way:
A1: 20000
B1: =A1*13% ==> 2600
In article <093f01c3861f$4a6902d0$a301280a@phx.gbl>,
"Shawn S." <shawn3731@yahoo.com> wrote:
> What is the formula for using one cell to figure a
> percentage of another cell. Example:
> colume A colume B
> 20000 13% of colume A
> Thanks
...
From ListBox selection >labels on userform to show sheet cell valuI have 3 Userforms; the first contains a ListBox from which user select a
specific Quotation number (column A on underlying worksheet). The first
UserForm also contains text boxes that, upon a "cmdShow" button go and get
relevant data from the row chosen by the user in the ListBox and show these
values in the textboxes on UserForm1. All fine.
On Userform2, I do not want user to have to choose Quotation number from the
same ListBox (also on Userform2) in order to populate the textboxes on
UserForm2 (different cell values from same row as User selected when on
UserForm...
Formula using hoursHow can I create a formula that will say:
If this cell is higher than 9:01 but but lower than 9:04
show a number?
Thanks for your help!
Hi Ali,
=IF(AND(A1>TIME(9,1,0),A1<TIME(9,4,0),17,"")
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Ali" <anonymous@discussions.microsoft.com> wrote in message
news:731101c3e72a$ceb69220$a501280a@phx.gbl...
> How can I create a formula that will say:
>
> If this cell is higher than 9:01 but but lower than 9:04
> show a ...
Excel Formula #21
Thankyou McGimpsey for your quick reply appreciated, and the frormula
also works
--
SAMSON
...
Create value list from a range of values
I've got a range of cells containing state abbrev. that I want to lis
in another range excluding all duplicated states abbrev's.
Is there a way to list the non-duplicated values automatically with
function?
I've tried using the excel Data Filter menu but it seems that needs t
be performed manually. I need a function that automatically calculate
when there is a change or addition to my list of states.
Thanks for your help..
--
mallets12
-----------------------------------------------------------------------
mallets123's Profile: http://www.excelforum.com/member.php?action=...
Maintain cell reference after value is movedHow can you keep a reference to another cell from changing even if the other
cell's contents are moved?
The objective is to maintain a column of running summations, such as a
projected bank balance, that refer to anticipated credits or debits in
another column, even as those entries are moved about.
For example, cell G100 has the formula =SUM(D$4:D100), which works only
until the value in D100 is moved, say to D150, whereupon the formula in G100
is automatically changed to =SUM(D$4:D150). I can copy and paste the value to
D150 and then just delete the old value in D100...
Add values of cellsI need to sum the values of a column of cells with both
positive and negative numbers, but I want to exclude the
negative numbers?
Use:
=SUMIF(D2:D5,">0")
With D2:D5 replace by your range.
--
Message posted from http://www.ExcelForum.com
Hi
try
=SUMIF(A:A,">0")
--
Regards
Frank Kabel
Frankfurt, Germany
B2 wrote:
> I need to sum the values of a column of cells with both
> positive and negative numbers, but I want to exclude the
> negative numbers?
...
Advanced Vlookup FormulaHello, I have a list of unique values in Column A in sheet 1. In
sheet2, I am have data laid out in a table with row headers and column
headers.
I am trying to come up with a formula that will display the value that
intersects in sheet 2, based on the combined column header and row
header.
So for example.
Sheet 2
Column Headers
Row Headers State City Weight
Jeff TN Nashville 200
Tim FL Miami 155
Eric GA Atl 225
Sheet 1...