|
More site info...
Worksheet Functions | Forum profile
|
|
Forum profile page for Worksheet Functions on http://www.officefrustration.com.
This report page is the aggregated overview from a single forum: Worksheet Functions, located on the Message Board at http://www.officefrustration.com.
This forum profile page summarizes the general forum statistics such as: Users Activity, Forum Activity, and Top Authors, which are reported in either a table or graph below for a given reporting time period.
Additional forum profile information for "Worksheet Functions" on the Message Board at http://www.officefrustration.com is also shown in the following ways:
1) Latest Active Threads
2) Hot Threads for Last Week
Warning: These statistics are generated using 'best efforts' and can experience delays and reporting errors at times. Please note that such statistics do not constitute a forum's popularity and/or exact posting volumes at any given reporting period.
|
|
|
|
|
Posting activity on Worksheet Functions:
|
|
Week
|
Month
|
3 Months
|
|
Threads:
|
389
|
1,466
|
4,024
|
|
Post:
|
1,073
|
4,424
|
12,798
|
|
|
Worksheet Functions Posting activity graph:
|
Top authors during last week:
user's latest post:
Using Format Painter on multiple...
Published (2009-11-09 19:58:00)
Tried in 2007 and works similar to 2003 If this post helps click Yes --------------- Jacob Skaria "pj" wrote: I used to be able to double click the Format Painter to paste formatting to multiple cells but now I'm using Excel 2007 when I double click the Format Painter the button just pops out so I can only ever past the formatting to one cell. It only stays in when you press it once. Is there somewhere else that I have...
user's latest post:
How do you combine a date field...
Published (2009-11-09 22:37:00)
Not sure what all of your possible time formats look like, so using your base formulas... How about this: =DATE(YEAR(NOW()),1,D14-378)+TIME(E15/100,MOD(E15,100),0) Custom format as m/d/yyyy h:m -- Biff Microsoft Excel MVP "Janet Kreinbrink" wrote in message ... I have a data file where column A = date (as a number value) and column B = time (in "general" format). Example: A B 422 1700 423 1350 424 800 425...
user's latest post:
Can Excel look thru column of...
Published (2009-11-09 23:00:00)
Find the most frequently occurring number: =INDEX(A1:A367,MODE(IF(A1:A367"",MATCH(A1:A367,A 1:A367,0)))) Find the most frequently occurring name: =INDEX(B1:B367,MODE(IF(B1:B367"",MATCH(B1:B367,B 1:B367,0)))) Both of these functions are committed with Ctrl+Shift+Enter, not just Enter. HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''....
user's latest post:
Converting Pounfs to US Dollars
Published (2009-11-09 18:37:00)
Put 1.6767 in a spare cell and copy, then Edit/ Goto/ Special/ Constants to select the right range of values (and not your formulae), and Edit/ Paste special/ Divide (or Multiply, see below). Are you sure that it's $1 = 1.6767 pounds and not the other way round? Or has the pound had a VERY bad day on the exchanges? :-( -- David Biddulph "Tom" wrote in message ... Assistance please. I have a spreadsheet (P&L) in...
user's latest post:
Convert currency to general
Published (2009-11-09 22:49:00)
Format to general to remove the currency sign. Second part I don't understand........remove the , and . but retain the decimal places. If you delete the . there are no decimal places. Try this........................... Enter 100 in an empty cell. Copy that cell. Select the range to change and Paste SpecialMultiplyOKEsc. Returns 148067 Gord Dibben MS Excel MVP On Mon, 9 Nov 2009 13:24:01 -0800, Adam wrote: Hi, I was wondering is there...
user's latest post:
Data validation error message...
Published (2009-11-09 23:54:00)
In the Data Validation dialog, on the Error Alert tab, make sure the "Show error alert after invalid data is entered" checkbox has a check in it. Hope this helps, Hutch "raphiel2063" wrote: I've set up a cell with data validation from a list and given it a reference list from another worksheet (using a name). I've gone into the error alert tab and set a message but nothing is happening if I type...
user's latest post:
DAVERAGE, MIN, and MAX from...
Published (2009-11-08 09:11:00)
Hi, The DMIN, DMAX functions will work well only for the answer in one cell. Copying and pasting down will cause problems. While there is workaround to that problem but that would entail using Data Tables. If it easiest to work with thr MIN(if and Max(if array formulas. Someone has already helped you with those in another thread you started. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Shelina" wrote...
user's latest post:
IF statement and Combo Boxes
Published (2009-11-08 16:56:00)
On Tue, 3 Nov 2009 13:28:01 -0800, cm wrote: I would suggest using a data validation list rather than a combo box. Create your list and then refer to the list as follows: Choose Data Validation (on the Settings tab) Allow 'List' and then in the 'source' box you will put the cell references for your list values. I make an array of cells containing the desired drop down box list, and then NAME that array. You can name...
user's latest post:
Week ending calculation
Published (2009-11-09 21:40:00)
Glenn wrote: Michelle wrote: I am working in a sheet where I have a date and I am trying to find the Friday post date. I am using =DATE(YEAR(E2),MONTH(E2),DAY(E2)-WEEKDAY(E2)+6) It works for everyday unless the day is a Saturday. I've tried several other formulas (found on the discussion board) and get basically the same results. Michelle Actually, your formula works fine, but can be simplified to this: =E2-WEEKDAY(E2)+6 Make sure to...
user's latest post:
Running total for 2 columns IF...
Published (2009-11-09 14:33:00)
=IF(G1="letter",C1*E1,IF(G1="tabloid",C1*E1*2)) On 9 Lis, 14:26, paulab wrote: Can anyone help, I am trying to keep a running total of impressions based on daily entries throughout the month: If column G = "letter" than *column C (sets) * column E (pages) If column G = "tabloid" than *column C (sets) * column E (pages)*2 Thank you in advance, paulab
|
|
|
|
Latest active threads on Worksheet Functions::
Started 1 day, 18 hours ago (2009-11-10 03:49:00)
by Tom Hutchins
Here is one way...
On another sheet (Sheet3 in my example), put the shift 1 names in A2:A13.
Put the shift 2 names in B2:B13. On the main sheet (where you will specify 1
or 2 in A1), enter this in A2 and copy down through A13:
=INDIRECT(ADDRESS(ROW(),$A$1,,,"Sheet3"))
Enter 1 or 2 in A1. Hope this helps,
Hutch
"Elton Law" wrote:
Dear expert,
Would like to prepare the ...
Started 1 day, 16 hours ago (2009-11-10 05:52:00)
by T. Valko
Based on your sample data this portion returns 0:
=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)
And this portion returns 5:
SUMPRODUCT((A1:A6="C")*(B1:B6="D"),C1:C6)
Combined, the result is 5.
So, I have no idea what this means:
= 1 + 5
= 6
--
Biff
Microsoft Excel MVP
"sam" wrote in message
...
hi everyone,
here is my issue. i am using SUMPRODUCT for a ...
Started 1 day, 16 hours ago (2009-11-10 05:38:00)
by ryguy7272
Time to start learning about Access:
http://www.datapigtechnologies.com/AccessMain.htm
HTH,
Ryan--
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
"drrons" wrote:
I use Excel 2003 as a database for a large mailing list. When I receive a
change, is there a way I can enter it into one file and have it appear in all
other files for ...
Started 1 day, 23 hours ago (2009-11-09 22:34:00)
by Sean Timmons
In a helper column:
=SUBSTITUTE(SUBSTITUTE(A2,"$",""),".","")
"Adam" wrote:
Hi,
I was wondering is there any way to convert currency to a general format (or
text).
I need to export information from my AP application to my bank. However,
the query comes out of the AP application as a currency field, and the bank
wants it as text.
1e.
$1,480.67 needs to have ...
Started 1 day, 21 hours ago (2009-11-10 00:41:00)
by Pete_UK
Try these:
1:
=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100= " Prime")*(ISNUMBER
(D1 100)))
2:
=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100= " Prime")*(D1 100))
3:
=SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1 100)))
Hope this helps.
Pete
On Nov 9, 11:30*pm, Dave wrote:
I have a spreadsheet I use for sales management and need help with 3 formulas
I cannot figure out....
Started 1 day, 21 hours ago (2009-11-10 01:08:00)
by Pete_UK
You can't use full-column references with SUMPRODUCT in XL2003 or
earlier.
Also, I suspect you have the numeric value 1 in column A and not the
text value "1", so change your formula to this:
=SUMPRODUCT((A2:A669=1)*(B2:B669="A1"))
Hope this helps.
Pete
On Nov 10, 12:02*am, fay wrote:
Hi,
Can anyone help me with this?
I've read all the threads regarding alternatives...
Started 1 day, 17 hours ago (2009-11-10 04:48:00)
by T. Valko
One way...
=IF(A1=400,100,A1*IF(A1=1500,0.25,IF(A1=2499,0. 3,0.35)))
--
Biff
Microsoft Excel MVP
"jato" wrote in message
...
trying to figure this out, im in commsionned sales, and we base our
commision
off a percentage of our gross, i need a formula that basically does this
400 or less=100
1500 or less *.25
1501-2499*.3
2499*.35
all in one formula.... is ...
Started 1 day, 17 hours ago (2009-11-10 05:00:00)
by T. Valko
One way...
Assume data starts in cell A2. Enter this formula in B2 and copy down:
=IF(MOD(ROWS(B$2:B2),5),"",AVERAGE(OFFSET(A2,,,-5 )))
--
Biff
Microsoft Excel MVP
"bill gras" wrote in message
...
Hi All
I have a column of 1020 rows with numbers , I need to average the sum
of every 5 rows down to 1020 rows eg-
a d
1 52
2 60
3 64
4...
Started 2 days, 18 hours ago (2009-11-09 04:25:00)
by T. Valko
If there's no other numerical data beyond the last entry to the end of the
row just expand the range:
=SUM(AI49:IV49)-SUM(AI48:IV48)
--
Biff
Microsoft Excel MVP
"sjm182" wrote in message
...
I'm not sure if I'm asking this correctly, so please help me clarify if
needed!
first example:
=SUM(AI49:AN49)-SUM(AI48:AN48) in which "AN" is regularly extended (AO,
AP,...
Started 1 day, 21 hours ago (2009-11-10 01:03:00)
by Pete_UK
Assume that your table is in Sheet2, then you can put these formulae
in another sheet in the cells stated:
A2: =INDEX(Sheet2!A$2:A$4,INT((ROW(Sheet2!A1)-1)/5)+1)
B2: =INDEX(Sheet2!B$1:F$1,MOD(ROW(Sheet2!A1)-1,5)+1)
C2: =INDEX(Sheet2!B$2:F$4,INT((ROW(Sheet2!A1)-1)/5)+1, MOD(ROW
(Sheet2!A1)-1,5)+1)
These assume that you have five columns of data in your table (i.e
columns B ...
|
|
Hot threads for last week on Worksheet Functions::
Started 1 week ago (2009-11-04 02:47:00)
by Pete_UK
It doesn't look as if any cells in column C contain 0 (or does your -
indicate 0 ? If so, then there are 4 of those).
Generally you would have a formula like this:
=SUMPRODUCT((B2:B10=3)*(C2:C10=0))
if you want both conditions to be satisfied at the same time before
you count that row, or this:
=SUMPRODUCT((B2:B10=3)+(C2:C10=0))
if you want either of the conditions to be ...
Started 1 week ago (2009-11-04 15:08:00)
by Bernie Deitrick
Abdul,
=$C$1-D4 + 1 - SUMPRODUCT(--(WEEKDAY(ROW( INDIRECT(E4&":"&C1)),1)= 6))
I'm not sure if you want 2 or 1 when you use two dates that are consecutive days - if you want 1,
then remove the +1, otherwise, leave it.
HTH,
Bernie
MS Excel MVP
"Abdul" wrote in message
...
Hi,
I am using the following formula to get number of days passed
excluding Fridays in a given ...
Started 6 days, 10 hours ago (2009-11-05 12:01:00)
by Mattlynn via OfficeKB.com
Another way of saying this is...
In Column D, i would want to say that if Column A = Retail Direct, and Column
B = 25500 sales value. then the answer should be 6
Or
If Column A =Distributor, and Column B = 200,000 then the result should be 25
Hoping someone can help !!
Thanks
Matt
Mattlynn wrote:
Hi,
I have data in 3 Columns
Sales Group (Col A) Sales Value (Col B) TM ...
Started 2 days, 6 hours ago (2009-11-09 15:38:00)
by Gaffnr
Hi Rebekah,
Im sure there is a better way to do this but the below will work.
In column c do: =and(A1=10,B1=2008).
this will produce a true/False result.
Then simply do a countif of on true in column C
--
Rob Gaffney
"Rebekah" wrote:
I'm hoping somebody can help me, as I feel this formula should be simple, but
I can't get it to work!
I have 2 columns
A...
Started 1 week, 1 day ago (2009-11-03 20:03:00)
by Luke M
Assuming it is truly just the format that's causing the display (and not text
inputted as a date) you can just use the MONTH and YEAR functions.
Example:
=MONTH(SerialDate)
See XL help file for further detail.
--
Best Regards,
Luke M
*Remember to click "yes" if this post helped you!*
"TDMP" wrote:
Hello, I have a sheet that contains dates in column (W) formatted per ...
Started 1 week ago (2009-11-04 00:19:00)
by glenn external usenet poster
I tried to write a formula to count how often the value of a range of cells
falls between two numbers. for example in the chart below:
row value
1 25
2 27
3 36
4 37
5 42
I tried the formula =countif(a1:a5,and(35,40)) and my result was "0" when
it should be "2".
Can anyone help correct my formula?
...
Started 1 day, 23 hours ago (2009-11-09 22:34:00)
by Sean Timmons
In a helper column:
=SUBSTITUTE(SUBSTITUTE(A2,"$",""),".","")
"Adam" wrote:
Hi,
I was wondering is there any way to convert currency to a general format (or
text).
I need to export information from my AP application to my bank. However,
the query comes out of the AP application as a currency field, and the bank
wants it as text.
1e.
$1,480.67 needs to have ...
Started 1 week, 1 day ago (2009-11-03 06:16:00)
by Jacob Skaria
Large returns negative..Try the same formula with the below data in
A1:A9...to see that it returns -6 which is the 6th largest of the lot.
-1
-2
-3
-4
-5
-6
-7
-8
-9
If this post helps click Yes
---------------
Jacob Skaria
"BuckinB" wrote:
In Excel 2007
How do I get the formula: =large(a1:a9,6) to return negative numbers, I
only get positive.
Started 5 days, 19 hours ago (2009-11-06 02:34:00)
by Jacob Skaria
Try this formula
=IF($BN$2:$BN$31=1,$A$2:$A$31,"")
'To apply to all cells in one stretch
--Copy the above formula as text to clipboard
--Select the range say BO2:BO31.
--Press F2. and paste teh formula to the active cell
--Press Ctrl+Shift+Enter
If this post helps click Yes
---------------
Jacob Skaria
"bob" wrote:
This is a array formula. If BN2:BN31 = 1, then display ...
Started 5 days, 23 hours ago (2009-11-05 22:58:00)
by Sean Timmons
in your column B,
=IF(ISNA(VLOOKUP(A1,[Workbook2.xls]Sheet1!A:A,1,0 )),"No Match
Found",VLOOKUP(A1,[Workbook2.xls]Sheet1!A:A,1,0))
"Gotroots" wrote:
Workbook1
Sheet1
A
1 car
2 boat
3 plane
4 train
5 motorbike
Workbook2
Sheet1
A B
1 moterbike formula returns âmotorbikeâ
2 car formula returns âcarâ
3 lorry formula ...
|
|
Size: 1,400 bytes
