A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Search Forums
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Showing results 1 to 25 of 43
Search took 0.23 seconds.
Search: Posts made by: bapeltzer
Forum: Worksheet Functions January 27th, 2010, 10:57 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 1
Views: 54
Posted By bapeltzer
HOW CAN I IMPLEMENT SUMIF(D1:D6,"AVERAGE(D1:D6)")

Move the average function out of the quotes; while there it's being treated
as part of the text string rather than being evaluated:
=sumif(d1:d6,"" & average(d1:d6))


"Bill Friedman" wrote:

The...
Forum: Worksheet Functions January 7th, 2010, 02:53 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 3
Views: 85
Posted By bapeltzer
number times a value appears in a column beside it chronological

Sounds like a COUNTIF function, where the top of the range is fixed and the
bottom of the range is the current row. So if your data is in column A and
your count in column B, you could enter...
Forum: General Discussion December 30th, 2009, 01:51 AM Posted to microsoft.public.excel.misc
Replies: 1
Views: 98
Posted By bapeltzer
Switching ODBC sources in Excel 2007

I usually now modify ws.listobjects(1).querytable.connection rather than
ws.querytable(1).connection. You should be able to modify the 'for each'
loop in the kb article you referenced.

"MichaelS"...
Forum: General Discussion December 15th, 2009, 01:19 AM Posted to microsoft.public.excel.misc
Replies: 2
Views: 51
Posted By bapeltzer
Averaging Time

If your time is iin cell A1, use the formula =round(a1*48,0)/48
The reasoning is that you're asking to round to the nearest 1/2 hour. To
Excel, 1 is a day, so a 1/2 hour is 1/48.

"Jim" wrote:

...
Forum: General Discussion November 14th, 2009, 10:35 PM Posted to microsoft.public.excel.misc
Replies: 1
Views: 92
Posted By bapeltzer
formula problem

=sumproduct(--($B$1:$B$6="-50%"),--($C$1:$C$6="cover"))
Used this way, the sumproduct is like a multi-criteria countif (if you're
using Excel 2007, you might check out the countifs function).
I'm...
Forum: General Discussion November 14th, 2009, 10:30 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 88
Posted By bapeltzer
Vlookup only the last 6 characters

The RIGHT function is returning a text value, and I'm guessing that the
values in column J are true numbers. So you'd need to covert the text back
to numbers in order to have the lookup work...
Forum: Worksheet Functions November 12th, 2009, 05:38 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 6
Views: 60
Posted By bapeltzer
Need formula to round number up to always end in X.X9

=ROUNDUP(A1+0.01,1)-0.01

This adds a penny, rounds up to the next dime, then subtracts a penny. If
you don't first add the penny, you could wind up lowering the input value.
Ex: 2.60 would...
Forum: General Discussion August 26th, 2009, 01:27 AM Posted to microsoft.public.excel.misc
Replies: 1
Views: 52
Posted By bapeltzer
SUMPRODUCT

Besides the criteria for column N, you also don't have the $ range you want
to total. In the formula below, I've assumed it's in column B, but you can
adjust as...
Forum: General Discussion July 24th, 2009, 02:15 AM Posted to microsoft.public.excel.misc
Replies: 1
Views: 45
Posted By bapeltzer
Formula help

=LEFT(A1,FIND("SMTP",A1)+3)

"tom" wrote:

In Excel 2007 I would like help writing a formula to remove text everything
right of the SMTP (see below). All of this infomation is in cell A1 so it...
Forum: Worksheet Functions June 8th, 2009, 10:50 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 6
Views: 111
Posted By bapeltzer
sum for match criteria

Now that you've got criteria on multiple fields is when I switch from sumif
or countif to sumproduct. If you want to get the total cost of 'abc' parts
in 2007 (I'll assume the part number is in...
Forum: Worksheet Functions June 8th, 2009, 06:04 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 6
Views: 111
Posted By bapeltzer
sum for match criteria

=sumif(A:A,"=" & date(2007,1,1),D:D) - sumif(A:A,"=" & date(2008,1,1),D:D)
will add up the costs for parts purchased in 2007. The first sumif counts
everything purchased on or after 1/1/07; the...
Forum: General Discussion May 31st, 2009, 09:25 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 69
Posted By bapeltzer
Dates Workdays

The approach I'd take:
For each month, find that month's start and end dates. Then the task's
start date for that month is MAX(task start date, month start date). The
task's end date for the...
Forum: Worksheet Functions May 17th, 2009, 07:22 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 2
Views: 75
Posted By bapeltzer
Index/Matching in combination w/Right()

I'd use a function within the MATCH to force your 4-digit code to match the
codes in your table... So if your table's codes are 6-digit text strings,
then instead of matching A13, I'd match "00" &...
Forum: Worksheet Functions April 23rd, 2009, 05:00 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 4
Views: 59
Posted By bapeltzer
Converting decimals to Time

The /24 is right, but don't use the text function; you want the data to
remain numeric in order to calculate an average!
If you just divide by 24, then format the cells as Time, you should get the...
Forum: Worksheet Functions April 20th, 2009, 04:45 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 2
Views: 53
Posted By bapeltzer
DCOUNT Help

Try '=Luc

"Rob B." wrote:

I am using the output from a combobox as a variable in a database formula
(DCOUNT). The variable is a name. My problem occurs when one name is used
that is also...
Forum: General Discussion April 18th, 2009, 01:12 AM Posted to microsoft.public.excel.misc
Replies: 2
Views: 64
Posted By bapeltzer
Multiple cell unit conversion. Please help.

Enter your conversion factor, 2.54, in some random cell. Select that cell
and ctrl+c to copy. Highlight all the cells you want converted. Right-click
and select Paste Special, select divide and...
Forum: General Discussion April 12th, 2009, 06:12 PM Posted to microsoft.public.excel.misc
Replies: 1
Views: 90
Posted By bapeltzer
[newbie] financial accounting problem

Is there a reason not to use the built-in present value function? With 180
payments remaining, it's just =-PV(0.75%,180,1206.93)
(or, using your cell references, =-PV(D18,D19-D20,D17) )

If you...
Forum: General Discussion April 12th, 2009, 05:46 PM Posted to microsoft.public.excel.misc
Replies: 3
Views: 76
Posted By bapeltzer
SumIf based on date

=sumif(b:b,"=" & k1,c:c)
This assumes that you only have dates (not dates w/ times) in column B.


"Harry" wrote:

I have dates in Col B, and quantity in Col C. I want to enter a date in cell...
Forum: General Discussion April 9th, 2009, 05:50 PM Posted to microsoft.public.excel.misc
Replies: 5
Views: 52
Posted By bapeltzer
Help with the CONCATENATE formula

The concatenate will pull in the value but not the format. You can dictate
the format using the text function:
="Your service will expire on " & text(Sheet2!D32,"Mmmm d, yyyy") etc

"tech1NJ"...
Forum: General Discussion April 5th, 2009, 01:06 AM Posted to microsoft.public.excel.misc
Replies: 1
Views: 67
Posted By bapeltzer
Focing a cell to be used

Use an absolute reference. Where you now have b1, use $b$1.
(There are also mixed references. $b1 would keep the column fixed as you
copy across; b$1 would keep the row fixed as you copy...
Forum: General Discussion April 4th, 2009, 12:43 AM Posted to microsoft.public.excel.misc
Replies: 2
Views: 52
Posted By bapeltzer
Concantenate columns with dates and text

Use the TEXT function to format the date as you wish it to appear, eg:
=TEXT(A1,"Mmm d, yyyy") & B1

"Dazed and Confused" wrote:

The first two columns in my spreadsheet are dates. The third is...
Forum: General Discussion March 29th, 2009, 04:06 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 52
Posted By bapeltzer
date comparison

The first criterion would be like
="=" & DATE(2009,3,14)
It's a string, but with the date converted (via the Date function) to a
numeric value.
The date could also be in another cell, so your...
Forum: General Discussion March 21st, 2009, 12:23 AM Posted to microsoft.public.excel.misc
Replies: 6
Views: 69
Posted By bapeltzer
Wrong amount formula

Your formula is picking up 2% of everthing plus another 5% of the excess;
thus the excess contributes a total of 7%. I think you want
=(C4*2%+(C415651.89)*(C4-15651.89)*3%)

"George" wrote:

...
Forum: General Discussion March 16th, 2009, 01:42 PM Posted to microsoft.public.excel.misc
Replies: 4
Views: 70
Posted By bapeltzer
formula to look up and sum the Grand totals based on the Heading

If you've got the 'Grand Total' labeled in column A and want to find 'Grand
Total' and return the value in the same row from column B: =vlookup("Grand
Total",January!A:B,2,0).
Another approach...
Forum: Worksheet Functions March 14th, 2009, 08:22 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 3
Views: 48
Posted By bapeltzer
Excel sum with variable start column

Well, you can use 'index' to reference a particular entry in your list.
Again assuming that your label, 'Units 2008' is in column A and the data in
column B, you can calculate the sum beginning...
Showing results 1 to 25 of 43

 
Forum Jump

All times are GMT +1. The time now is 10:54 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 OfficeFrustration.
The comments are property of their posters.