Computer Support Forum

Solved: Excel Formula if a date is exceeded

Question: Solved: Excel Formula if a date is exceeded

I'm looking for a formula for excel, but can't seem to fine tune one, or find an existing one.

The spreadsheet is for applications. If initial contact happens on say 7/20/2010 and the applicant has 5 days in which to respond, for example. I want a column that posts the due date for follow up automatically, and then also a column that says either NO or YES in regards to Follow Up Needed that also updates automatically based on Date Of Follow Up having passed.

I hope this makes sense. I'm going to attempt to put an example below.

Application Date Applicant Name Date Of Follow Up Follow Up Needed
7/20/2010 John Smith 7/26/2010 YES
7/25/2010 Jane Smith 7/31/2010 NO

EDIT: Well, it won't maintain the spacing, but that should give you an idea of what I mean.

Relevance 100%
Preferred Solution: Solved: Excel Formula if a date is exceeded

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Solved: Excel Formula if a date is exceeded

11 more replies
Relevance 86.92%

Sorry for the newbie question, but here goes:

I am using Excel 2007 and I have a spreadsheet that uses 2 dates -- the buy and sell date. I have a column that calculates how long we held the product. I use an IF statement to put a zero in the column if the product hasn't been sold yet which looks something like this:

IF(ISBLANK(buy_date),0,(sell_date - buy_date))

Now I want to summarize the "time-to-sale". I want to count all "0" sales (unsold), all sales between 1-30 days, 31-60, 61-90, through the whole year. The problem is, I can't use a macro because this spreadsheet needs to be "macro-free".



Answer:Solved: Excel formula -- by date summary

7 more replies
Relevance 86.1%

Is there a way to write a formula that adds down the column 1 day to the previous day, but when it hits a Saturday or a Sunday, it goes to Monday?
ie: (a1+1) but by the time it got to April 3 it would know to add 3 to get to Monday
Monday, March 30, 2009
Tuesday, March 31, 2009
Wednesday, April 01, 2009
Thursday, April 02, 2009
Friday, April 03, 2009
Saturday, April 04, 2009
Sunday, April 05, 2009
Monday, April 06, 2009
Tuesday, April 07, 2009

Thanks for any information you can provide.


Answer:Solved: excel date formula - ignore weekends



If WEEDAY of previous cell = 6 (=Friday) then add 3 else add 1

3 more replies
Relevance 83.23%

HI guys,

I am using Microsoft Excel 2003 and am trying to create a scheduler for my work place.
What I have to do is allow the user to enter data in the input sheet (by input meaning Dates) and then accordingly scan the schedule sheet and find the date entered on the input sheet, following which print the appropriate schedule opposite that date for that particular project.

For Example .....
If a person is working on a project called apple in cell D (effectively making it the 3rd project in the list) and the user enters the project start and end dates as 5th June and 13th June. So what I have to do is scan the Schedule sheet for these dates and enter the appropriate text of Start and End before these dates in the D column (i.e under the respective project).

How to do this I am clueless as I am a complete idiot as far as excel is concerned. SO any idea or any sort of help as to how this can be achieved will help.

The sheet must be automated as far as possible and there are about 25 to 30 projects which need to be updated into the sheet before the final output.

Thanks in advance.

Answer:Solved: Formula to find a date and enter schedule in a specific cell in Excel

13 more replies
Relevance 76.26%

I am trying to create the following pivot table formula:=IF"duedate(E2)>13,todays date(H2) true, # of days overdue<13=0, false)What I am trying to calculate is if it was due on this date 12/10/10, and todays date is 2/12/11, how many days is the document overdue. I need to put this in a formula that excel can understand. Can anyone help?

Answer:Date formula in excel?

I'm assuming the due date is in E2.This will return the difference between Today's date and the date in E2:=DATEDIF(E2,TODAY(),"d")Nested within an IF function, it could be used like this:=IF(DATEDIF(E2,TODAY(),"d") >13, "The item is overdue", "The item is not overdue")Note: The Help file for DATEDIF is missing in Excel 2003. If you want to learn more about it, look here: Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 76.26%

Hi,I am looking to see if there is a formula where I have a date in cell B1, and the formula results in the date in C1 being 7 days later than B1, and the date in cell D1 is 14 days later than that in B1. Also, in a reversal, so the date in A1 is 7 days prior to that in B1....Can anyone help me please? ThanksPaulEdit: Subject line edited by Office moderator to be more specific.

Answer:Help with Excel Date formula

Format your 4 columns as a Date.You are entering a date in B1 so in A1 use =B1-7In C1 use =B1+7In D1 use =B1+14HTHBryan

2 more replies
Relevance 76.26%
Question: Excel date formula

I have an excel spreadsheet with rows of service event start and end dates, grouped by id number:
2008/01/05 2008/01/06
2008/01/12 2008/01 14


2008/02/01 2008/02/10
2008/02/02 2008/02/02
2008/02/03 2008/02/03
2008/02/11 2008/02/14
I need to calculate the number of days between the end date and the next service start date - example 1 above is straightforward as it's A3-B1: 2008/01/12 - 2008/01/06

Example 2 gets trickier because the first service event spans 9 days, and other service events occur within that period - I need to find the difference between the last event date (2008/02/10) and the next one after that - (2008/02/11). In my 5 full worksheets of data, the number of rows between the valid entries is not constant - it could be the third row or the thirteenth. Does anybody have any ideas? many thanks!!!!

Answer:Excel date formula

6 more replies
Relevance 76.26%

I have to do this for class I have excel 2010, but the numbers are not coming out right. Help please?cell a-2 1/1/2009 cell b-2 9/1/2009 cell b-3 ={[(B2-A2)-280]/7}*-1That is what is in the book it is to find out Babys gestational age in weeks. This formula would not work at all, I removed all the {[ and it worked but the sum is not correct, any ideas????Thanks!edited by moderator: Title Edited To Be More Specific

Answer:Help Excel Date Formula

Read up on Calculation operators and precedence, I believe that is the point of the formula.The { [ characters are there as a visual aid.MIKE

4 more replies
Relevance 76.26%

I am trying to create a formula that will add a range of cells if there is a date (any date) in the corresponding cells.In column C(rows 4 to 1334) there are counts entered. In column H, there are dates entered if the corresponding counts have been received. I want a formula at the bottom of the page that will add up the counts in column C, but only if there is a date in the corresponding cell in column H (cell is no longer empty).I'm pretty sure it's a sum if formula I need, but the formula below does not work. So, I'm pretty sure I'm missing something obvious.=sumif(H4:1334,>0,C4:C1334)I would be very appreciative of any help you could offer.Thank you.

Answer:excel formula for sum if date

Not quite right, try this:=SUMIF(H4:H1334,">0",C4:C1334)You left off the column indicator in the first range and in the criteria to match section, if your doing a comparisonit needs to be surrounded by quotes. IE: ">0"MIKE

7 more replies
Relevance 76.26%
Question: Excel Date Formula

I record the date of when I send a out a letter to customers, I send up to 25 of these letters a day. Once 28 days passes inc weekends I need to send out a reminder to the customers that receive the letters and then another 28 days later send out a second reminder . The ver of Excel I am using is 2002. Is there a formula I can type in to calculate the above?Thanks in anticipationTim

Answer:Excel Date Formula

I am not sure if I have completely understood the question but:- In column A you have the date of the original,in column b enter a1+28 and in column c enter b1+28. Make sure that all the cells format is sent is set to date.

4 more replies
Relevance 76.26%
Question: Excel date formula

HiI have a spreadsheet which includes the start date for a number of employees.I want to work out when they will have completed 25 years service.Please could someone tell me what formula I need to use to add 25 years to each date.ThanksKJ

Answer:Excel date formula

With a date in A1=DATE(YEAR(A1)+25,MONTH(A1),DAY(A1))

2 more replies
Relevance 76.26%

Hi... I need to find out the remainer of months in the year times an amount... Example:8/20/12 141.00I would like to know what the formula would be for this date till the end of the year times the 141.00. Can you help?Thanks,Mel

Answer:excel DATE formula help

I need to find out the remainer of months in the yearYou can use the =DATEDIF() command.If your data looks like:
1) 8/20/12 141.00
In cell C1 enter the formula:=DATEDIF(A1,"12/31/2012","m")*B1MIKE

4 more replies
Relevance 76.26%

Need help setting up a date formula in Excel '97.

Have first date of today, have second date of next available appointment. Need Excel to formuate the turn-around time (i.e. today 4/6/04, next appt date 5/5/04 = 29 days.

How do I do this??? It's driving me crazy. It's probably something quite simply that I'm not think of!


[email protected]

Answer:Excel 97 Date Formula Help!

This is what you are looking for. (I think)

2 more replies
Relevance 75.44%

I am using a spreadsheet to track tasks and would like to automatically change the status of the task to "Due" when current date is 15 days from the task due date, and "Late" when current date is equal to or after the task due date.Thank you in advance for your assistance.

Answer:Excel Formula: Help with IF using current date

With your Deadline Date in cell A1 enter this formula in B1:=IF(A1-TODAY()<=0,"Late",IF(A1-TODAY()<15,"Due",""))See how that works for you.If you want a more Colorful way to notify you of an upcoming deadline,try using Conditional Formatting to color the cell, see this How-To:

3 more replies
Relevance 75.44%

Hi, how to add a sum for a date lets say 2/3/2012, i want a formula which will autopopulate the date automatically to 2/08/2012 that is 2/03/2012 + 5 months, please help

Answer:Date formula in excel 2007

Search Excel for edate function.

3 more replies
Relevance 75.44%

I need help making a formula calculating 90 days before a certain date, i.e. if something is due on Aug 31st, 2012, how do i make a formula showing 1st submission due 90 days earlier, i.e. May 31st, 2012? Thanks

Answer:Excel Formula To Calculate Date

First, a posting tip:When you post a question in a help forum such as, please use a subject line that tells us something about your post.If everyone used a generic subject line such as Excel Formula, we wouldn't be able to tell one question from another.I have edited the subject line of this thread.As far as your question...With your date is A1, it's as simple as =A1-90DerbyDad03Office Forum ModeratorClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 75.44%

My knowledge of excel is limited.I want to set up a simple spreadsheet template for rent received. I have tenants who may chose to pay rent weekly, fortnightly or monthly.Start with a cell that says their rent is xxx.xx ($300) pw Say their rent is $300 per week.In column A I enter date rent received.The period starts on say 1st March 2010 (to be shown as 01/03/2010 european format) and $300 would pay their rent up to 07/03/2010. So that when I enter 300 in cell B1 it shows up as (paid to) 07/03/2010 in the adjoining cell C1. If they paid 320.50 it would show up as (a credit) of 20.50 in the next adjoining cell D1 and if they paid 280 it would show as -20 in D1.If they paid 620 on 01/03/2010 it would show rent paid to 14/03/2010 C1 and a 20 credit in D1. Also in colum E a running total of colum D which would show if they had any accumulated credit or debit.

Answer:Excel: formula Rent$ = date

There may be a Template available for download from Microsoft's Office Site.However, since you have Excel, I assume you have Access. There is a Real Estate Template right in Access that allows you to manage rental properties. Perhaps you'd like to try that.LIR

15 more replies
Relevance 75.44%

I want to create an excel spreadsheet where ...If I type a "1" and/or "PARTIAL" in cell A1 then cell B1 will autofill with the current date but if there is nothing in cell A1 then cell B1 will be blank as well.I hope I got my point across. I've been asking around and trying to google it but I haven't been able to find anything. If any of you know how to do this that would be great :)

Answer:Excel Formula: Help With IF and Current Date

In cell B1 enter the formula:=IF(OR(A1=1,A1="PARTIAL"),TODAY(),"")Be advised, that by using the TODAY() function,the date will change to the current date every time you open the sheet.So today B1 will show 01/18/2012but tomorrow it will show 01/19/2012MIKE

16 more replies
Relevance 75.44%

Hello,I am trying to construct two excel formulas to help determine a bill date, which always falls on the 9th of the month. Sign up dates occur throughout the year.In column AV, I have a set of dates that represent signups. The bill date should be as follows.1. If the sign-up date in AV2 is prior to the 9th, make the bill date in AW2 be the 9th of that same month. (June 3, 2012 sign up becomes June 9, 2012 bill date.)2. If the sign-up date in AV2 is after the 9th of the month, make the bill date in AW2 be the 9th of the following month. (June 10, 2012 sign up becomes July 9, 2012 bill date.)3. If the sign-up date in AV2 is the 9th of the month, make the bill date in AW2 the 9th the following month. (June 9, 2012 sign up becomes July 9, 2012 bill date.)Any help would be much appreciated! I am no Excel expert, but I do have "some" of the formula possibly figured out. =IF(AV2<="DATE(9)"Any help would be much appreciated!

Answer:Excel bill date if/then formula

The layout of your question doesn't make sense. You've got bullet 3 before bullet 2.Bullet 2 says to use the same month if the sign up date is the 9th, but your example uses the following month. (June 9, 2012 sign up becomes July 9, 2012 bill date)In addition, you said: "...a bill date, which always falls on the 9th of the month"".Then, under bullet 3 you said: "June 10, 2012 sign up becomes July 10, 2012 bill date".How can the bill date be the 10th if the bill date always falls on the 9th?It wouldn't make sense to offer a solution to a question that we don't quite understand.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

5 more replies
Relevance 75.44%

On a spreadsheet I use a formula to tell me that an date entered in a cell is now 6 months old by bringing up the word 'UPDATE'. The formula I use is:
This might not be very elegant but it works.
I would like to make it do the same for a row of cells, eg: C5:C15 rather than just C5 (as in the above example). I only want a single notice in one cell to tell me that one or more cell entries in the defined row needs updating.
In other words one (or more) of the entries in the range C5:C15 is more than 6 months old and needs updating!
Is that possible?

Answer:Excel Date formula help wanted

I'm convinced that a =if(or(a4,b4,c4))<=... structure is the way to do it but I'm damned if I can work out the syntax using edate(a4,6) in place of A4 etc. Might point you in the right direction or someone else might know?

6 more replies
Relevance 75.44%

I am trying to combine information from a couple cells to create a date header for a column in a worksheet.

I want the cell to read:

Date: Thursday October, 21 2003

I can get the first two parts but the date comes back as a number:

Date: Thursday 37917

Here is the formula:

= "Date: " &'[WKH Daily Roster.xls]Sheet1'!$C$1& '[WKH Daily Roster.xls]Sheet1'!$D$1

The last part is the date "$d1$1"

I know this is probably simple but I searched help in Excel and on Micros.. site with no luck.


Answer:Format date within Excel Formula

Something along these lines?

= "Date: " &[Book1]Sheet1!$A$1&" "&TEXT([Book1]Sheet1!$A$2,"mmmm, dd yyyy")


3 more replies
Relevance 75.44%

Hello all, I am new to this site and forums, so please bear with me!

I am trying to create a formula from a row of connecting cells that will select the earliest date in the list or give an answer of "None" if there aren't any dates in the list.

13-Jun, 17-Feb, 22-Nov, 05-Oct, 01-Jun. The result should be 17-Feb.
13-Jun, None, None, 05-Oct, 01-Jun. The result should be 01-Jun.
None,None,None,None,None,None, The result should be "None".

Many thanks in advance with this problem


More replies
Relevance 75.44%

I plot my blood pressure 3x/day all year long. For example, the dates are shown as:
I'd like very much to block and copy a years worth of dates from my 2016 file showing 2016 and convert all the year dates to 2017. Is there a relatively simple formula in Excel (Win-7/ Office Pro Plus 2010) I can use?

Answer:Date changes in Excel - Formula required

This may not be the most elegant method, but it is easy... And since no one has come to your aid...
Copy the date column (2016 dates) and paste it into your new sheet as column B.
In cell C1 use the formula =B1 + 365 where cell B1 is your first date
Copy the formula in cell C1 down the column (C2, C3, C4, etc) to all the other C-cells
Hide Column B
Now only the new dates show in the new spreadsheet. Not elegant, but easy & fast. (Be sure to copy the format so your dates appear as you want ie 30-Dec-16)
Hope this helps..

1 more replies
Relevance 75.44%

I have a spreadsheet where I need a cell to calculate the date (from a cell in column B) plus 14 days to get a due date, but leave the due date cell blank if there is nothing yet entered into column B. What formula should I use for this?

Answer:I need an If/then formula to add a date in excel 2010

Dates can be added just like numbers. Add 1, you've added a day; add 14, you've added 2 weeks.An IF statement can perform a calculation IF the logical_test argument is TRUE:=IF(B1="","",B1+14)If B1 is equal to nothing, return nothing, otherwise return B1+14.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 75.44%

Sirs,In my sheet I want to implement day as numeric value and will show for the month and month font should be displayed as text and capitalized and Sundays will automatically be highlighted by red color.this is to prepare a sheet with the days of the month be calculated automatically every month.pls pls help.

Answer:excel formula to calculate date

for example, =text(today(),"mmmm") will show the month as "August" (want this should be in capital font)and from next rows it will start displaying the value as followings:row: A1 B1 C1 ... to z1value: 1 2 3 ,,, to last day of the month (i.e. if it is Jan then 31 if Feb then 28/29 etc.) Sunday font color should be red colord.Not willing to use macro/vb.

2 more replies
Relevance 74.62%

I have compiled a monthly stats sheet for my department with a number of formulas. This sheet has been in use for three years with very little changes and only minor issues. This month, I'm having trouble and I can't solve the problem. In four columns, I have staff entering dates and the formula calculates how many client's fit in the specific column.

Formula in first three columns (D-F):

The second formula calculates in the date is above a specific date:

As stated, I've had minor issues with these formulas (sometimes I need to go in and "refresh" by just hitting enter and it recalculates. The problem this month (and it's occuring on three of eight sheets) is that when they go to enter into that column, the formula provide a '0' for the answer. I can go in and refresh (on my computer) and the formula will fix. But when they go back on and enter a date, it does back to zero (for all four colums). I've tried going on their computer to fix, but it won't let me refresh. I even rebuilt the page for one to see if that would help (it has helped with other issues in the past). This problem is happening on ALL the pages with these two formulas (so all 12 months are having the problem).

Any thoughts? I have a sense that it's some sort of software update that they've received and I haven't but who knows (two staff sit bes... Read more

More replies
Relevance 74.62%

I am trying to find a formula for counting up to a calendar date from a calendar date. That is, column A, row 1 is the umber of days to count up. Column B row 1 is the date I want to count from, Column C row 1 is the result. It would look like this when finished

33 1/3/2008 2/5/2008

Is such a formula possible?


Answer:Excel 2003 formula for adding to date

Well this does it, but I am not sure if you should use a date diff function

1 more replies
Relevance 74.62%

I am trying to find out the correct way to write a formula in Excel. We are trying to first determine if something falls into a date range, such as between 6/1/2011 and 6/15/2011 in row D. If something does fall into that date range, then we want to add up the corresponding amounts from row E to be summed together for us. Just to give you the correct ranges, we are looking at the dates from D5:D700 and the number amounts from E5:E700. Can someone please help me out? Thanks so much!

Answer:Correct Formula for Date Range for Excel

re: "row D, row E"Just to avoid any confusion, D & E are Columns, not rows.These formulas should get you what you are looking for. Note that these formulas will include 6/1 and 6/15. If by "between 6/1/2011 and 6/15/2011" you mean that you don't want to include 6/1 and 6/15, drop the "equal signs" before both DATEVALUE functions.=SUMPRODUCT((D5:D700>=DATEVALUE("06/01/2011"))*(D5:D700<=DATEVALUE("06/15/2011")),E5:E700)=SUMIF(D5:D700,">="&DATEVALUE("6/1/2011"),E5:E700) - SUMIF(D5:D700,">="&DATEVALUE("6/15/2011"),E5:E700)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 73.8%

I need help with a formula. I have monthly invoicing for contract work. The contracts can be 1 - 5 yr. Each year the contract price changes.

I want to be able to invoice on a certain date and have the contract price automatically put into the invoice based on the contract priced date the invoice falls within.

An example:
If invoice date (E12) falls:
Between date range H20-I20, place J20 (Amount) into D21 (amt) cell.
Between date range H21-I21, place J21 (Amount) into D21 (amt) cell.
Between date range H22-I22, place J22 (Amount) into D21 (amt) cell.

This would go on for a total of five options all based on the invoice date falling within one of the ranges.

Answer:Excel Formula for multiple date range options

7 more replies
Relevance 73.8%

I really need help!!! lol... I'm trying to have the document update from a master sheet which I managed to do via a VLOOKUP, but when the reference date on the document is updated, and I want it only to reflect the past 5-days, rather than the whole amount of information. So at all times it will show the last 5 work days and shift the cells automatically accordingly.

So basically the dates will constantly switch back. So that way tomorrow when I have the results of Sept. 29th, Sept. 22 will no longer be depicted, and the 23rd will take it's place; the 24th will take the 23rd's place, and so on. So it would look something like this...

**5 columns each date with it's own column
****After each day, there will only be 5 columns reflecting the last 5-workdays!

Sept. 22 Sept. 23 Sept. 24 Sept. 25 Sept. 26

More replies
Relevance 73.8%

Hi, I'm trying to come up with a formula that will calculate the impact of hiring for certain positions in Q2 based on the date of hire. For instance if I hire a new resource on May 1at at an annual salary of $50,000, what formula, based on that hire date, can i use in the columns for April, May, and June to reflect the impact ($0 in April, $4,167 in May, $4,167 in June)?

Answer:Hire date monthly impact formula in excel

I should have clarified: the goal is to be able to run different scenarios by changing the hire dates for potential resources....thx!

3 more replies
Relevance 73.8%

I use a multi sheet spreadsheet at work to track outstanding tasks.

I would like to write a formula to take today's date and hide the row if it is:
1- less than a week since the last entry
2- hide the row if it from the current month.

I would also like to write a formula that hides the row or column if all cells in use in that row or column have been checked.

Thank you in advance for your assistance.

Answer:EXCEL - using a date dependant formula to hide rows

Is it necessary to hide the row? The only way you might be able to do that is with a macro, but I think it would be complicated and messy. A more simple way would be to use conditional formatting - for example you could make the text a light grey (or even white) if the conditions are met. It wouldn't hide the row, but it would mean the other rows stand out by comparison.

6 more replies
Relevance 72.98%


I'm mail merging with Word a huge contract document and need to put in the phrase 'this contract is made on the [seventeenth] day of [April] [2010]'. Even in a custom format I'm struggling. When the contracts are printed off they need to be 'today's date' ie the day they are printed.

More replies
Relevance 72.98%

I haven't been able to quite get my functions right on this one and am hoping for some guru help.

I have a spreadsheet with dates in Column A (Contract Received) and Column C (Contract Filed). Not every cell has a date in one or both fields, as it is a tracking tool for work in progress. However, a contract must be Received before it can be Filed, so C will (er,should) never be blank if A is populated. I want to know the number of days lapsed between date Received and date Filed, and if date Filed is null, then number of days from Received to today's date. If the contract has not been Received, then no calculation should be performed. In spite of requests not to, users sometimes put text notes in these fields, so would be nice to ignore text. At bottom of column, I need to get an average of the time lapse on all filings to track productivity. This doesn't sound so difficult to me, but somehow I haven't gotten it quite right in the last two hours and it's time to ask an expert. Thanks in advance for your help!

- k

Answer:Excel 2010 Date Calc Formula, Ignore Blanks

Hi k, welcome to TSG.

Try this and let us know if it gets you close to what you need:


It basically starts with column A and determines whether there's a number there (a date, as opposed to text or blank). If not, it doesn't do anything, but if so, then it does the same thing with column C. If C has a number (date) then it subtracts A from C. If not, it subtracts today's date from A.

Let me know if I've misunderstood the question.

1 more replies
Relevance 72.16%

I need help trying to come up with a formula to calculate the sum of values (row 3), for the previous Friday to Yesterday's date i.e. Wed would sum Friday to Tuesday; Thursday would sum Friday to Wed...and Monday would sum Friday to Sunday.

My example is not posting correctly...but essentially Row 1 has Day headers (Monday, Tuesday, etc.); Row 2 has the actual date that corresponds with day header (3/7/2011, 3/8/2011, etc.); Row 3 has the values that I am wanting to sum according to criteria mentioned above. So for TODAY, 3/9/2011, I would like to sum the values of Friday, 3/4/2011, through Yesterday, Tuesday, 3/8/2011...and so on...
Greatly appreciated!

Answer:MS Excel 2007: Formula needed for sum of values from Friday to current date

9 more replies
Relevance 72.16%

Our employees can sell such things as vacation time, for example, 1 day of vacation pay but their request must be submitted by a certain time if they are to be paid. We can tell them what day they will be paid based on the date they submit their request. This can be confusing for a supervisor to explain and I wanted to automate it on an Excel sheet.
1. Employees receive their regular check every Friday as well as a check for any time sold.
2. As long as they get it into us within 7 days prior to Friday they will receive their check the next Friday.
3. If it’s greater than 7 days then it would be the Friday after next before they received the sold time check.
4. For example If an employee requested on May, 12th 13th, 14th, 15th, 16th, or 17th to sell vacation time that check would be received by the next Friday i.e. May 25th. However, if they turned it in on May 18th. 19th. 20th, 21st, 22nd., 23nd, or 24th. they would receive it on June 1st.
5. This pattern continues throughout the year. Note that weekends are included.
6. I think the attached will better explain what I am trying to do.

Answer:Formula in Excel for a Date to Remain Constant Based on a Range of Dates

I think there is something wrong in the way you explain it but, could be my interpretation though
I adden a column using the Days360() function which shows the nr of days between the request date and the 'friday' date, maybe you can use that resulty to compute the new value.

2 more replies
Relevance 68.06%

I’m doing a mail merge and a date that I would like to merge to my letters is found in 2 different columns on my .csv data sheet. Column A has mm/dd and shows “1106” for November 6th and Column B has yy and shows “7” for 2007. The data is formatted as a number – when I format the “1106” to a date it shows “1/10”. Is there a formula to take the data from both cells and format it to show “11/06/2007”? I'm using Excel 2000 and XP.

Answer:Solved: Date Formula

16 more replies
Relevance 68.06%

A person retires at the age of 58 years. If his date of birth is on any day between 2nd and month end.
Ex: if DOB is 15-10-2000 date of retirement will be 31-10-2058

This formula works OK for above

But if the date of birth is on 1st day of the month his retirement will be last day of previous month.
01 Oct 2010 his retirement date will be 30 Nov 2058
01 Jan 1996 his retirement date will be 31 Dec 2033

I want both of them together using if or any other mode of formula

Pl. help me with suitable formula

Answer:Solved: Help in date formula

10 more replies
Relevance 67.65%

Hey guys,
I have a question about coding and setting an excel file to send emails. I am using an excel file to track maintenance work orders and I would like to get an email each day for each work order that is requested to be finished on that days date. I have attached the excel file I am using, and the column that is important is C, "Date Needed" - If that date is today's date, the cell turns red and I would also like to get email notification about it. A separate email for each cell that matches today's date.

Also, if possible, I would like for it to look at all the sheets, not just the current month's sheet. For example, if a work order was filed in July but wasn't needed until September, I would still like to get the email reminding me that it needs to be finished on xx date in September.

I have attached the excel file I want to use.

I have searched all over this forum and others and have found many posts similar to this, but I am not a great manipulator of code, so I cannot get any of the other solutions to work.

Any help would be greatly appreciated, thanks!

Answer:Solved: Using Excel 07 to Send Emails When Date in Column = Today's Date

16 more replies
Relevance 67.65%

can anyone help me with a formula line In Crystal Reports when designing a report i need a formula that will colored the field if maximum number is exceeded.for example: if a specific field has reach a maximum number of 15, this field has to colored in red.

Answer:formula colored field if maximum number is exceeded

Although someone might come along who knows "Crystal Reports" it is a bit specialised. You might be better advised to go to their forum: pop back and let us know the outcome - thanks

2 more replies
Relevance 67.65%

I am trying to create a formula in that:

Col A will be a date of birth typed in as normal(13/06/1967)

Col B will be a formula calculating age on todays date with todays date changing automatically on a daily basis

(All I need displayed is the age of that person today everyday)

Doesnt have to be two columns because I dont need to show the date of birth.

Hope someone can help me, Thank you.

Answer:Solved: Excell formula for date and age

7 more replies
Relevance 66.83%

I am trying to create job status sheet, and I want the current date to show whenever a cell or row is added.

For example, If I put a job number in B2, I want the date(year, month, day) to show in cell A2 automatically. The IF formula is only formula I can think to use.

Answer:Solved: Using the IF formula to show current date

Just put the formula =Now() in the target cell. And each time the worksheet will be recalculated the formula will be updated, so no need in and if function.

2 more replies
Relevance 62.73%

Hi all,

Please tell the formula to calculate days in excel.

i need to subtract lots of dates like this, please give me the formula.

Eg: 01.02.2012 -12.02.2012=12 days

N.Prem Kumar

Answer:Solved: Excel formula

if you do a replace and change the . to a /
then format the cells to date and
then you can just take one from the other

But that would calculate as 11 days

if you need to still see in the for format
do a custom format and set to


1 more replies
Relevance 62.73%

Hi guys,

This request will be a easy one for you guys.
Can any1 provide me formula for excel so that when i paste a name in Cell A, Cell B should auto populate to the email id.
Example: Cell A ( George Memphis ) Cell B should be ([email protected])

i have also attached a example spreed sheet.
Please guys any help will be very help full. As this is done for over 500 users every 3 hours at work.

Answer:Solved: Formula for excel

8 more replies
Relevance 62.73%

I would like to write a formula for the following information:

If order 5-10 cases then 10% discount and if you order 11 and over cases, then 15% discount.

Answer:Solved: Excel Formula

9 more replies
Relevance 62.73%

I know this is probably very simple, but I am tryng to come up with a sum formula that will sum all the data in a column as long as it meets specific criteria in another column.

For Example:
Column A

Column B

I would like to sum all of Column B (excluding the values where Column A is equal to 123)

Can this be done?

Answer:Solved: Excel Formula -> Sum

6 more replies
Relevance 62.73%


This is going to be a bit tough to explain and is not really all that Excel related - more of a math question:

I have to be able to assign a (single) unique number (no matter which numer) to a unique set of whole numbers.

That is, I need to distinguish between, let's say, {2,3,6} and {4,7}. So I'm trying to find a formula that will always give me a unique number for a uique set. (The sets are random, but only whole numbers 1-9 are used ( kind of like sudoku)).

I thought this would be an easy problem, but I have yet to find a simple formula that will give me such a number. Simply summing the numbers won't work of course- in the example above both sums are 11 (not unique). I have also tried summing the squares of individual numbers, standard deviation, variance... and all of these fail to give a unique number for each unique set.

Is there such a formula? Any idea is a welcome!

Answer:Solved: Excel formula

11 more replies
Relevance 62.73%

I would like to create a formula that creates sequential numbers for rows that match the criteria of having "apple" in column A AND, 99324 to 99328 OR 99334 to 99337 in column C.

I want to following:
Column A Column C Column Q
apple 99324 1
apple 99327 2
apple 99334 3
apple 99335 4
apple 99337 5
I tried using the following formula and it did not work:
=IF(AND(A7="apple",C7>=99324,C7<=99328,if(or(A7="apple",C7>=99334,C7<=99337),MAX($Q$2:$Q6)+1," ")))
Please help!!!

Answer:Solved: Excel Formula If AND/OR

i made a mistake in the formula, and have corrected it. nevertheless, my point is still the same. the formula does not work. when i use it i get an error message

3 more replies
Relevance 62.73%

I have a couple of formulas that are not working the way I would like them to. Any assistance would be appreciated.

1. The first is simple, but for some reason is working.

='Associate 1'!D3

All I am trying to do is copy the contents of the cell from one worksheet to another to eliminate duplicate data entry. I have done it many times, but for some reason it is only showing the formula and not the cell contents.

2. The second is showing #DIV/0! until data is present and I want it to remain null until data is present. I have tried testing the data in an IF statement and returning null if no data is present, but I cannot get rid of the error message.


Again, any help is appreciated and if you need more information don't hesitate to ask.

Answer:Solved: Excel Formula Help

6 more replies
Relevance 62.73%

I have a formula in a cell to use the FIXED function. My formula looks like this:

=If(B9>5,Fixed(C9*L9),C9*L9) -> The result comes out to be 6

This part works fine. The formula above is located in cell E9.

I then have another formula in a cell (L9) that references the cell E9. However, the result that is diplayed in L9 is 6.3.

How can I get the formula in L9 to see the actual displayed value of 6 that is found in E9?

Answer:Solved: Excel Formula(s)

9 more replies
Relevance 62.73%

i am trying to come up with a formula for the following data.

1 300.00 142 =(100*A1)
2 250.00 75 =(75*A2)
i would like to be able to multiply the amount in column "B" up to 100 by the amount in column "A"
the way i do it now is column "C" i put in the formula (100*a1)
the problem is that sometimes the value in Column B is less than 100. For example - line 2
It is a large spreadsheet, so i would like one formula to apply. now i have to enter a different formula for values in column B that are less than 100.

i tried an IF formula but it would not work. my logic was. if = 100 then mulitply that amount by col. A if less than 100 then multiply that value by col A.
any ideas

Answer:Solved: Excel formula help!

8 more replies
Relevance 62.73%

The following code is behind my Excel spreadsheet.

Private Sub Workbook_Open()

Dim myCount
Dim i

Application.ScreenUpdating = False

For Each vCell In Range("D2:I2")
If IsEmpty(vCell) Then
Exit Sub
End If

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="time"


Application.ScreenUpdating = True

End Sub

How can get the code to check "D9:I9" as well?

Answer:Solved: Excel Formula

16 more replies
Relevance 62.73%

Can anyone help me figure this out? I am running out of time for a dead line and this is the last stumbling box I have.
I have 16 rows and at the end of each row is a total in the last row is the bottom of the column that totals the rows above it. If any of the 16 rows are <40 I want it to total them in the bottom row if they are >40 I dont want a total in the bottom row and I would like it to highlight the box if there is a sum in the bottom row and not highlight if it is empty.

I am using Office 2007

Answer:Solved: Need help with a formula in Excel please

I am using office 2003. I can get more help this site. the end of each row is a total in the last row is the bottom of the column that totals the rows above it. If any of the 16 rows are <40 I want it to total them in the bottom row if they are >40 I dont want a total in the bottom

3 more replies
Relevance 62.73%

need help fast - EXCEL 97 formula
formula entered =sum(h4:h5)<=30
results in answer of TRUE or FALSE
I need the actual number displayed

Answer:{Solved} excel 97 formula

6 more replies
Relevance 62.73%

Guys I would appreciate any help if possible with a excel formula. Here is the scenario I have two groups of data on the same spreadsheet 4 columns total. I need to compare column B to column D and if column B is equal to a value in column D then I need it to compare the corresponding record in A with C and if they match I need a simple Yes or No. See below if this is confusing:

Column A Column B Column C Column D Ans
testerjoe 1234567 testerjoe 9876543 NO
testersam 5555555 testersam 5555555 YES
Any help would be great I can get it to tell me if column A is in C but I can't figure out the rest...Thanks in advance.

Answer:Solved: Excel Formula Help

16 more replies
Relevance 62.73%

Hi. I need help with the following formula:


Where F6 is a set value and G... is a series of numbers I'm updating every so often. The problem is that if I paste that formula in several cells, they are filled up with the F6 value. Is there a way to have those cells empty if the G... value is empty?


Answer:Solved: ISO help with an Excel formula

=IF( G15="","",PRODUCT($F$6,G15))

3 more replies
Relevance 62.73%

Running XP Professional, Excel '97 - yes I know it's VERY old! (I am also running Excel on my new IMAC, but can sit in my comfy chair with my old, pc laptop)

I am running a sales event where several sales reps are selling samples. The columns are the different reps, the rows are the different sample items. I would like to be able to enter the price in the rep's column AND total the customer's purchases while entering the prices only once. I would like a formula that says if the value in a cell is >$0, enter that value in the column where I calculate the customer's purchases. After that customer's purchase is complete, I want to erase the prices in the customer calculation column, while leaving the prices in the reps' columns. At the end of the event, I need to be able to divy up the money based on the totals of the reps' columns. Thanks.

if (d2:g24>0, .......................

Answer:Solved: If formula in Excel

16 more replies
Relevance 62.73%

In the attached .xls I have 5 tabs in total – 4 (A,B,C,D) with columns indicating Budget, Actual, etc., plus 1 Summary Worksheet.

I want to transfer the numbers (under ACT column “E” only in the A B C D tabs) to the ‘summary’ sheet without having to do it the long-way. Not all tabs have the same acc numbers - they have some in common - but not all; however, all acc numbers are on the summary sheet.

I thought a VLOOKUP – but I’ve only ever done one before a few years back – and I'm not sure if it would work in this case.

Much appreciated!

Answer:Solved: Excel - Not sure which formula to use?

Try this

3 more replies
Relevance 62.73%

I'm trying to find a formula for cell 4 which uses cells 1-3. I will give an example of what cells 1-3 will be and then what I need 4 to be.

Cell 1=35
Cell 2=44
Cell 3=5

So now I need Cell 4 to equal 4. The formula I need is similar to C1+/-5 (so 30-40) and then C2-40. Or if Cell 2 was 26 then I would need it to be the 30-C2. In both cases I need Cell 4 to say it equals 4 when Cell 2 is both 26 and 44. At the same time though cell 3 won't be fixed so if cell 3 was equal to 6 then cell 4 would equal 3 when cell 2 is 26 or 44. I know this is kind of confusing, I just don't know how to explain it.

Answer:Solved: Excel Formula help

11 more replies
Relevance 62.73%

I need an excel formula that calculates rental rates at $82 per day for the first 90, $61.50 per day for next 90 days, and $45.10 per day thereafter. I have an IF statement that will calculate the first 2 rates. I just don't know how to add the third.

Any help would be greatly appreciated.

Answer:Solved: Excel Formula

8 more replies
Relevance 62.73%


I'm looking for a little help with an Excel formula. It is for a study plan I am creating. I think it should be an SUMIF something, I just can't quite figure it out.

I have my list of subjects in column C, a few other columns, a notional study time in F and my actual study time in G.

Further down I am doing a daily update on which subject I spent my time on in column C (using a drop down list) and the hours I spent on it in D.

So the formula I'm trying to create is for my actual study time, in column G. What I want it to do is, if any of my range of data from the daily updates equals the Subject then it should display the total number of hours I spent on it, which have been entered into column D, and this should carry on down my spreadsheet for all 8 subjects.

I hope this makes sense and is possible! Any help would be much appreciated

Penny x

Answer:Solved: Excel Formula help

7 more replies
Relevance 62.73%

Is there a way to do the following in Excel.

I have a cell that contains data that is the sum of 2 or 3 other cells. Then that cell (the sum cell) is used in another cell.

What i want to do is set it so that if the value of the sum cell is less than zero (if it is a negative number) to just treat it like a zero.

Is this possible? How so?

Thank you.

Answer:Solved: an Excel formula

11 more replies
Relevance 62.73%

Column A would be company, column B would be mail type, and column C would be the address. The numbers just represent the columns but would have no reference to the spreadsheet.
1 abc
2 abc billing po box 1
3 abc mail 801 smith street
4 acb mail 102 block
5 acb billing po box 3
6 acb

I want to be able to do a formula that will look at column a and b to get the address based on mail or billing how do i get this to work.


Answer:Solved: Excel 03 formula Help

16 more replies
Relevance 62.73%

I want to query a cell and if it is equal to or less than the numeral 2, have another cell display the text Minimal, if it is greater than 2 but less than 5 have said cell display Marginal and if 5 or over display Disaster Waiting to this feasible?

Answer:Solved: Excel IF formula

6 more replies
Relevance 62.73%

I have the formula below:

=If(abs(A1-A2)<=0.01, "Yes", "No")

The difference comes out to be -0.01, but using the ABS funtion the number is converted to 0.01. However, the formula is still producing a "No" in the cell.

What am I doing wrong? Why is the formula not recognizing that the values are equal?


Answer:Solved: Excel Formula

11 more replies
Relevance 62.73%

Howdy all

I have an excel sheet with an access authority in column A and a user ID in column B. If a user ID has more than one access authority they will be listed on multiple rows, as follows:

A 1
B 1
C 1
D 1
A 2
B 2
A 3

I have a list with 18,000 users and I am trying to perform two functions with this data but have had very limited success so far.

Firstly I want to create a new column that lists all the access authority any user has (such as follows)

A 1 A B C D
B 1 A B C D
C 1 A B C D
D 1 A B C D
A 2 A B
B 2 A B
A 3 A

I couldn't get a single formula to do everything I want so I've tried to split this into several formulas so I can firstly identify which users have more than one level of access (using an IF & COUNTIF formula) and then deal with the results. This bit does work OK. I've then tried to use this identifier with a number of different formulas to try and concatenate the relevant access levels into a single field. But it doesn't work properly (or at all most of the time) - I'm presuming I can't actually concatenate fields ONLY if the user ID is the same (which is what I think I want to do). I'm hoping soemone here can actually understand what I'm trying to say, and either point me in the right direction or tell me to sod off if it can't be done!

Secondly I ultimately want to strip out all multiple instances of each user so I'm left with only a single user and the re... Read more

Answer:Solved: Excel Formula Help

13 more replies
Relevance 62.73%


Quick formula question:

If F3 is not empty, It shows FALSE, instead of blank. How come?

Thank you


Answer:Solved: Excel formula

you have a few embedded IFs there so trying to debug them

is that forumla correct - seem to be missing the last FALSE statement for the first IF
also there is NO F3 in the formula

IF(Data!IF3="",IF(ISERROR(SEARCH("Car",Data!CL5,1)),"","X")),"")Click to expand...

Data!IF3Click to expand...

If i have split this correctly and coded in colour correctly - so
1st IF = Red - Has Test, TRUE , but no false
2nd IF = Green - has Test, TRUE, False
3rd IF = Blue - you can see that has the Test, True, False

= IF(

Test1: AND(OR(Data!B3=1,Data!B3=2,Data!B3=3),Data!D3="P"),

TRUE1: IF ( Data!IF3="", IF (ISERROR(SEARCH("Car",Data!CL5,1)),"","X") ,"")

, False1: ???

1 more replies
Relevance 62.73%

Can anyone tell me what the error is in this formula:


State refers to a cell ,g17, that I have named State.
Subtotal refers to a cell, h37, that I have named Subtotal

I am trying to get a sales tax amount that equals the subtotal times .0725, if Il or Illionois are entered in the state cell, if anything else is in the state cell then sales tax should equal 0.

My brain can almost grasp the problem, then I lose it....

Answer:Solved: Excel Formula


If I remove the equals sign before "Product", and the parenthesis that surrounded that part it works.



Silly me....

1 more replies
Relevance 62.73%

I have inherited an Excel 2007 macro workbook and am having trouble with a formula: =TEXT(ROUND(D9,2),"0.00")& " " & IF($C9<D9,"&#9650;", IF($C9=D9,"&#9679;","&#9660;")).

The increases and decreases are working fine, but the values that remain the same ($C9=D9,"&#9679;",) are displaying as an increase or decrease instead of a neutral. How do I fix this?

Answer:Solved: Need help with Excel formula

I figured it out. Even though my numbers displayed as x.xx, the actual number was x.xxxxxxxxxxx. As soon as I truncated, the formula worked.

1 more replies
Relevance 62.73%

if i have a formula in a cell ex:=IF(H7>0U9,"") the ,"" makes the cell empty untill something gets put into H7. Now the problem when i try to =sum the numbers in that column it gives me a error. I want to be able to add the cells with no numbers but has =IF(H7>0U9,"") in it, and if there is a number in there the =sum works just fine. Its only when the cells with that formula is left blank that it gives me the error. Any idea's? Thanks in advance.

Answer:Solved: Excel formula help

16 more replies
Relevance 62.73%


Please see attached Excel Doc,

I have set up a workday function to calculate the date a report is due.

If the report is late there is a column to enter the date it was chased.

I need the table to recognise when a date is entered in the DUE DATE column and
begin showing the Workday calulation from this column instead.

Can this be done? am I just stupid...

any help would be greatly appreciated.

Thank You

Answer:Solved: EXCEL FORMULA - Can you help? PLEASE!

Hi, welcome to the forum,

I editted your formula to include the chaser date. If this date is filled then the conditional format will be triggered by this.

Is this what you wanted?

You'll have to copy the formula down the rest of the list

3 more replies
Relevance 62.73%

I'm looking for some help from you kind people! Hopefully one/some of you can assist!

I'm looking for a formula that will compare on cell column A to all of column B and pick return a true/false -- yes/no answer. It is numbers, but it's formatted in text as to not lose the leading zero.

Thanks for any assisance you can offer...



Answer:[SOLVED] Excel formula!!!

8 more replies
Relevance 62.73%

I am sure this is easy but I can't work out how to do it.
I have a spreadsheet with Cols A to K
If there is an entry in Col B it will be a ref number for an existing client.
I then want Excel to refer to my client list (on another Excel sheet) and enter the values from Cols C,D,E and F into those cols on the new sheet.

Let me explain. The client list contains Col B ref Number, Col C First Name, Col D Last Name, Col E Address, Col F Email

Now when one of these clients makes a purchase I would like to just enter the ref number on the new worksheet and have it fill in the details
If there is no ref number then I want Excel to do nothing so I can enter the new information as needed.

But how?

Answer:Solved: Excel. Help with formula

11 more replies
Relevance 62.73%

I am in need of help with a formula. I have a set date that a tast occured. The next task must occur within 7 days of that first date. So I have a window of +7 days. When the actual date the next task occurred is outside of that window, I would like the cell or text to change colors.

Example 1:
Task Window is +7 days

A1 = 24 Jan 2012
B1 = 25 Jan 2012 (First possible date task can occur. Formula used "=A1+1"
C1 = 31 Jan 2012 (Last possible date tack can occur. Formula used "=A1+7"

I would like to enter the actual date the task was performed into D1. When that date is outside of the range from B1 to C1, I would like the font or the cell to change color.

Example 2:
Task Window is -7 days, +21 day

A2 = 24 Jan 2012
B2 = 17 Jan 2012 (First possible date task can occur. Formula used "=A2-7"
C2 = 14 Feb 2012 (Last possible date tack can occur. Formula used "=A2+21"

I would like to enter the actual date the task was performed into D2. When that date is outside of the range from B2 to C2, I would like the font or the cell to change color.
Can you help?


Answer:Solved: Excel Formula Help

8 more replies
Relevance 62.73%

I need some help getting a rate per hour on my sheet that I have set up.

Column H has the formula - "=E3*F3"
Column I has the formula - "= E3*G3"

Column J & K is what I need to know is correct.

Column J (CH#6) has the formula - "=H3+I3/45*60"
Column K (CH#7) has the formula - "=H3+I3/31*60"

45 and 31 equal current cycle time in minutes CH#6 and CH#7 takes to cycle. I am wanting to know how many shots per hour each CH# gets with the current cycle time.

I have attached a sample of my sheet.

Answer:Solved: help with formula in excel

If you're looking for say 320 in J3, then you need to add some brackets to force something called operator precedence:


1 more replies
Relevance 62.73%

I am trying to come up with a formula using an IF statement. I have ten cells that I need to check if the cell contains a certain text. I have tried to use the AND function within the IF statement, but the problem is that 5 cells will contain the text needed, and the other 5 are blank. Since the other 5 cells are blank, the IF statement is coming out false.

Is there a way to write the IF statement that will ignore the blank cells? However, some or all the blank cells may need to be filled in later.

Answer:Solved: Excel Formula

16 more replies
Relevance 62.73%

Hi all,

I have small issue.
I pull record for almost 1000 ppl every single day.
In excel cell A will be the Name of the user, which is in this pattern (Last name, First name).
And cell B is the email address for the user, which is in this pattern (First name.Last [email protected]).
What i need to do is i have to manually type the email address in cell B.
Is there a way by which i make it automatic. So that when ever i (Last name, First name) in cell A it it should auto-populate
the email address in Cell B.

Any help will be much appreciated as this manual work is a pain in the wrong place everyday

I have attached the example spreed sheet.

Answer:Solved: Excel Formula

7 more replies
Relevance 62.73%

I'm trying to make a formula for my grade sheets so I don't have to manually go in and type 650+ grades when grades close. Without going into too much detail- if the child's grade is higher than 1 I give them an "S." If it's a 0 or lower, they get an "N."

The forumula I tried was =IF(cell>0, "S", IF(cell<0, "N"))

What did I do wrong? I had a much more complicated one last year with 5 grades that worked- guess I missed something.


Answer:Solved: Need help with an Excel Formula

7 more replies
Relevance 62.73%

I am getting a "DIV/0" on the following formula:

=AVERAGE('Cadell:Assoc 22'!C10)

I would like to have it display nothing in the cell if it is going to return "DIV/0". I tried this:

=IF(AVERAGE('Cadell:Assoc 22'!C10)=ISERROR,"",AVERAGE('Cadell:Assoc 22'!C10))

It did not work. Can someone give me the right way to do this?

Answer:Solved: Excel formula

6 more replies
Relevance 62.73%

Could someone please help me out with this formula? Excel is saying that it is incorrect, and I'm not quite sure how to fix it. Here it is: =IF(C2<>C3,"",IF(AND(H2<>"ETA",H2<>"ROM"),"",IF(H2="ETA",L2=L3),"",IF(H2="ROM",L2=L3),"",1)).

I'm attaching that spreadsheet that I'm trying to apply it to. The end result that I am looking for is that if the reason equals ETA for a person that their job title is the same for that row and the row below it. The same goes for reason code ROM.

Answer:Solved: excel formula

In your file, all the combinations lead to a result of "", so you see nothing. If you try it out by replacing the 4 "" responses with something else, like "2", "3", "4" and "5", you will see values appear in the column - I did and got the following (from top to bottom) 4 4 3 2 3 4 3 3 3 4 3 3 3 3 3 3 3 3 3 3 3 3 3 2 3 3 3 3 2

1 more replies
Relevance 62.73%

Howdy all

Following on from Bomb's great macro this morning that sorted my thousands of users and their access levels (I found some had up to 26 different access levels), I've come across another problem that I can't solve.

The results have come out as per the sample below (I tried to upload the file but it's too big at 4 mb - I can send it to anyone if desired), which is exactly the results I needed, but I've found another problem which is the fault of the originating database (not Bomb's macro!). The access groups aren't in ascending order which I require and I can't for the life of me work out how to get all the access rows listed in ascending order without doing them one at a time (I tried using the sort function and it didn't really work - it changes everything but not actually into ascending order except for the first row).

Can someone much smarter than me help me out again? You can have my first born (although she's a little quirky).

11 195 194 30
12 0 62 70 333
43 68
55 65 204 208
59 208 203
66 312 311 334 378 275 279
69 203 34 355
71 204 65 34
77 34 204
83 187
87 34 204
88 34 204

Answer:Solved: Further Excel Formula Help!

6 more replies
Relevance 62.73%

Hi all,

My Excel skills are a bit rusty and hope you guys can help me out.
I am experimenting with a simple spreadsheet for the transport company where I work for.
Sheet 1 shows Locations with 5 columns
Column B Customer (user input)
Column C and Pallet spaces (user input)
Column D (at present not relevant)]
Column E Reg (user input)
Column F I would like to create a formula that looks at the vehicle reference sheet at the capacity of the corresponding registration and deduct the pallet spaces for the relevant vehicle
Each vehicle may travel to several customers and I would like to monitor how much ‘free pallet space’ they have.
(the same goes for location 2)
If I can get this to work, it potentially replaces the old fashioned white boards and instead we project the spreadsheet on the wall.

Thanks for the help guys!

Answer:Solved: excel formula

16 more replies
Relevance 62.73%

Request help on formula to hide if there is no value in referred cells.

Column A=date
Column B = Credit
Column C=Debit
Column D=Balance (i.e. D2+B3-C3)
Balance is required for whole month.
If there is no input either in credit or debit column Balance Column is to show blank

Please see attachment.

Answer:Solved: Excel formula help req.

6 more replies
Relevance 62.73%

Dear all,

Please find the attachment, Sheet (Zapmotail) Column N contains Either "GSC" or "N/A"

All I need is :

If N2,N3,N4 Etc... contain "GSC", the whole row has to be copied to Sheet "Unique"
If N2,N3,N4 Etc... contain "N/A”, the whole row has to be copied to Sheet "Not Under Scope"

Answer:Solved: Excel formula help

7 more replies
Relevance 62.73%

Can someone give me the formula to solve the following problem.

I need 5% of a number until the total reaches 5000 then I need 2.5% of everything over that amount.


150000 should equal 6,250

The first 100000*5% would equal 5000
everything over 5000 is taxed at 2.5% so
50,000 * 2.5% = 1,250

Thanks for your help.

Answer:Solved: Need a formula for Excel

6 more replies
Relevance 62.73%


I have a simple formula =((B5+C5)*0.034)+0.2 and all it does is calculate my paypal fees. However I do not always use paypal so I was hoping this could be incorporated something like this

if paypal is used calculate =((B5+C5)*0.034)+0.2 if no payment is made do nothing

Where b5 is item cost and c5 is postage cost,you see at the moment I have two spreadsheets ones for the postage and one for non postage and its a bit tedious to fill out.


Answer:Solved: Excel Formula Help

just use IF
You need something to tell if its a paypal transaction

IF ( test for paypal, ((B5+C5)*0.034)+0.2 , 0)

how do you know if paypal

3 more replies
Relevance 62.73%

I hope someone can help with this as I've tried everything I know.

This is the outcome I am trying to achieve.
if A is a negative number, show the result of A plus B
if A is a positive number, show 500

This is the formula I presently have and it works until the A1 is both <B1 and positive

All help much appreciated

Answer:Solved: Help with an Excel formula

=IF(A1<B1,A1+B1,IF(A1>B1,500)) does not compare for if A is negative, it compares for if A is less than B.

And welcome to the Forum!

3 more replies
Relevance 62.73%

I am normally ok with formulas but for some reason my brain just cannot grasp this. I know it is simple but I need some help.

I have a line that has a unit price, a discount amount, a corporate price (that auto-populates the discounted price) and then of course the last line is the line total that auto-populates depending on it which cost needs to be reflected.

Now the problem is in the corporate price cell- if there is no discount, it shows the actual unit price and I need it to be blank.

I have attached an example for review.

Thanks for any help provided, it is much appreciated.

Answer:Solved: Excel formula help

Might this help?
[If there is a Discount price, do this calculation, If there is no Discount Price, put "" in F22] (As opposed to F22 being 'FALSE')

2 more replies
Relevance 62.73%

I need help with another excel calculation here. I have attached a sample data to this string. I need to get the average resp and resolution time for the tasks for respective priorities and the average should not include "NA" value in that. Can you please help me?


Answer:Solved: Excel formula help

6 more replies
Relevance 62.73%

I never used Excel, but my wife asked me to help her with a homeschooling grade book in Excel.
I think I've done well. Except for the last formula which keeps coming up as #DIV/0. I think it's because I can't divide by 0. Any tips on how to correct this would be awesome.

It is a weighted grade book
C7=(M7/M6*.1)+(U7/U6*.15)+(AC7/AC6*.15)+(AK7/AK6*.2)+(AQ7/AQ6*.4) is the last formula where I'm having trouble

and these are samples of the fist operation source values
M6=SUM(E6:L6) total possible blank cells
M7=SUM(E7:L7) total received blank cells

thanks in advance

Answer:Solved: Excel Formula Help

6 more replies
Relevance 62.73%

Hi ! I want to place a formula in Q2:Q90 that says if H2:H90 is X and P290 is W than Q2:Q90 is 2 times O2:O90 thanks for any help this is important to me hope to here from some one soon.

Answer:Solved: Excel formula

16 more replies