Re: Shipping Charge Calculator
Today, it works for me, too. I have no idea why it wasn't yesterday.
Thanks again, everyone.
Re: Shipping Charge Calculator
Today, it works for me, too. I have no idea why it wasn't yesterday.
Thanks again, everyone.
Re: Shipping Charge Calculator
Thank you, Craig and Sicarii. Craig, your solution works perfectly to round up the shipping weight. Sicarii, I keep getting flagged with "The formula you typed contains an error". I'm troubleshooting it right now, but would appreciate any further help you may offer.
Thanks again.
I’m trying to add a shipping charge calculator based upon weight to a worksheet. I was trying to do with with VLOOKUP (my first attempted use of this) so I created a second worksheet called ‘Shipping’ which has the weights in column A (Weight) and the corresponding shipping cost in column B (Price). Up to 70 pounds, column A increases in two-pound increments (A2 through A36, 2 through 70 lbs); from 70 through 150 pounds, the increases are in five-pound increments (A36 through A52, 70 through 150 lbs). The total weight to be shipped is derived from SUM(D2:D30), located on the first worksheet called ‘Order’ in cell D32. In cell E32 I have entered this formula:
VLOOKUP(D32,Shipping!A2:B52,2).
My problem is if Order!D32 returns something like 4.5, the value for four pounds is the result, while the six-pound charge should be the result. If I round this value up to 5.0, 5.5 or even 5.9, I get the same result.
I know I’m not supposed to guess at what I need to do, but these are my thoughts: 1) I need to modify the VLOOKUP formula to select the next higher value, i.e., so the 6 lb charge is selected for E32 when D32 contains 4.5 lbs, or the 85 lb charge is selected for E32 when D32 contains 82 lbs, 2) I need to add a ROUNDUP function to the SUM formula; this would need to round up to the next even number up to 70 lbs and then in increments of five above 70 lbs. I don’t know if either approach is correct, and in any case, I couldn’t figure out the syntax for either of these.
I’ve attached a sample spreadsheet, Shipping_Charge.xls.
In advance, I thank you for your help.
Re: Add Text To Formula Result
Perfect!
Thanks, everyone.
Re: Add Text To Formula
Thank you, Fencliff. I knew it was something simple, but I couldn't find this when I searched.
Thanks again.
Another question: this formula reports the average out to twleve decimal places. How can I decrease this to two places?
Thank you.
I have a simple formula that calculates the average of a range of cells. I would like to include in the same cell the text "Average per Month". In other words, I'd like this cell to read [Average per Month: AVERAGE(B324:M324)]. I cannot add another column because other cells would need to be merged, which will affect sorting.
Thank you.
Re: Monthly Count Of Yearly Report
Domenic and daddylonglegs, thank you for replying. Your solution worked, daddylonglegs. From what I know about pivot tables, Dave is right. I need to find the time to sit down and learn how to do them.
Thanks again.
Re: Adding Conditions To Countif
Thanks, I'll try these. The raw data are inputted by two different systems, one of which includes the time with the date.
Again, thank you.
Re: Adding Conditions To Countif
Thanks, Domenic. Attached is the sheet for 2007. What I can't figure out is how to count how many patient visits each physician had per month.
Thanks again.
I have a spreadsheet that counts yearly physician referrals over a five-year period (2003 - 2007). There are worksheets for each year containing all patient and physician data, and the results for all years are summarized on a final summary sheet with this formula: COUNTIF('2007'!$F$2:$F$3547,A2), with this formula for the year 2007 and for the physician listed in cell A2.
I've now been asked to break this out on a monthly basis (love those bean counters...). I've divided the initial five-year sheet into separate years, with a worksheet for each month. I now need to count the number of times a physician's name occurs in a yearly list for each month. I haven't been able to figure our how to add something like IF('2007'!$F$2:$F$3547=A2)AND(E2 is between 01/01/2007 and 01/31/2007) to the original COUNTIF formula (I know the syntax is wrong). I know I could divide the yearly list into months, but this would create a pretty big sheet, and it would seem that I should be able to do this with just one yearly list.
Thank you.
Re: First Name-last Name To Last Name-first Name
The list is of physicians. Some have middle initials, some don't. There are spaces between the first and last names. The export data I receive are like this: John B. Smith, M.D. What I'd like to convert this to is: Smith, John B. The export data include patient names, account numbers, dates of service, the MD names, and more. I need the MD names in the last name-first name format so sorting can be better performed. Concerning code vs. formula, I'm not sure; ideally, there would be some form of lookup table (which I don't know how to do) that I could run that would have every MD name, so please tell me the best method to accomplish this. I'm sure I'd have to update this table as new MD's came on board.
Thank you.
[SIZE="3"]An application I use at work exports names in a first name-last name format. At this point I have a list of about 315 names in this format. Is there a way I can convert these names to a last name-first name format? I need to export these data on a daily basis, so I'll need to perform this conversion every day. I did a search for this, but didn't find anything.
Thank you in advance.[/SIZE]
Re: Displaying Cell Value But Don't Use In Calculations
Thank you, Trebor76. I don't know Visual Basic yet, but I hope to take a few Excel classes this spring, so maybe then I'll be able to do this.
Re: Displaying Cell Value But Don't Use In Calculations
The answer just came to me - if I put an apostrophe in front of the value, it's treated as text.
Thank you.
I have a row of raw quality control data, but some are not in control and should not be used in the calculation of statistical data. There are not a lot of them, so I can manually select these (I use a red fill color for the cell to signify bad data), but I would like to be able to keep these data displayed but not have them included in the calculations. Can this be done?
Thank you.
Re: Multiple If Arguments
Thank you, so much. This one's perfect!