Help, help! desparate for formula help!!

I need somone to tell me a formula to copy and paste into a cell in my excel 
worksheet.  I need this for work today!  Can't figure it out.  Here's what I 
need:
If SUM(C4:O4) is greater than 40, then SUM(C4:O4)-40, but if SUM(C4:O4)is 
equal or less than 40, then 0
Now how the heck do I make a formula with that?  Please help!
0
Utf
4/26/2010 10:50:01 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
565 Views

Similar Articles

[PageSpeed] 5

Try
=IF(SUM(C4:O4)>40,SUM(C4:O4)-40,0)

-- 
Jacob (MVP - Excel)


"How do I?" wrote:

> I need somone to tell me a formula to copy and paste into a cell in my excel 
> worksheet.  I need this for work today!  Can't figure it out.  Here's what I 
> need:
> If SUM(C4:O4) is greater than 40, then SUM(C4:O4)-40, but if SUM(C4:O4)is 
> equal or less than 40, then 0
> Now how the heck do I make a formula with that?  Please help!
0
Utf
4/26/2010 10:54:01 AM
=IF(SUM(C4:O4)>40,SUM(C4:O4)-40,0)



-- 
Regards
Dave Hawley
www.ozgrid.com
"How do I?" <How do I?@discussions.microsoft.com> wrote in message 
news:C3E55085-0C53-473E-82C2-1487DAFD5527@microsoft.com...
>I need somone to tell me a formula to copy and paste into a cell in my 
>excel
> worksheet.  I need this for work today!  Can't figure it out.  Here's what 
> I
> need:
> If SUM(C4:O4) is greater than 40, then SUM(C4:O4)-40, but if SUM(C4:O4)is
> equal or less than 40, then 0
> Now how the heck do I make a formula with that?  Please help! 

0
ozgrid
4/26/2010 10:55:55 AM
Hi,

IF statements aren't necessary, try this

=MAX(0,SUM(C4:O4)-40)
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"How do I?" wrote:

> I need somone to tell me a formula to copy and paste into a cell in my excel 
> worksheet.  I need this for work today!  Can't figure it out.  Here's what I 
> need:
> If SUM(C4:O4) is greater than 40, then SUM(C4:O4)-40, but if SUM(C4:O4)is 
> equal or less than 40, then 0
> Now how the heck do I make a formula with that?  Please help!
0
Utf
4/26/2010 11:19:02 AM
Reply:

Similar Artilces:

Help with Annoyances
I have two annoyances that I can't seem to get rid of in Outlook 2003: 1. Locking the toolbars; the Standard and Menu toolbars alwys move around; I'd like to "lock" them in place; is there anywhere to do this? 2. Custom toolbar removal; everytime i start Outlook 2003, the PDFMaker custom toolbar appears; when I Customize => select the toolbar, Delete, it works for the session only and next time I start Outlook it reappers; how do I get rid of this? Any assistance greatly appreciated! smoovee <smoovee@discussions.microsoft.com> wrote: > I have two annoyanc...

Help with this thing
It was working in the window "Transactions >> Sales >> Transactions of Sales", but exactly were publishing a Quotation, which i wanna print, more nevertheless was shut up to me network, then I closed the window and it threw several messages to me of error, and from that then it was blocked the quotation that was working. My question is as I can unblock this document? ...

conditional format formula
I am trying to use a formula in conditional format to turn a cell grey if the date in the column is not yet passed. not colored not colored should be grey a b c 10/20/04 10/21/04 10/22/04 5:00 5:00 5:00 =IF(NOW()<a$2,,) Is my formula. Whether I use < or > does not matter, th...

Conditional formatting with dates formula problem.
Hello. I appreciate help on this topic. I'm very new to excel's conditional formatting capabilities and I need help on the following: I have a worksheet where I am using columns A and B to be fashioned into a type of "reverse" library checkout card; I want to flag when 120 days have passed since an item has been checked out. All cells are blank with the exception of the formatting applied to cells in column A. For example, Condition 1 on cell A1 has the formula: =IF(ISBLANK(B1),(A1-TODAY())<120) **I've also tried =IF(ISBLANK($B1),($A1-TODAY())<120) My objecti...

Bug in Visual Studio 2005 Listview object. Help!
I've found an obvious bug in VS2005 (VB.NET), in the Listview object. I desperately need a fix. To duplicate do the following: 1. Create a new Visual Basic Windows Application. 2. Add a Listview object and Textbox object to the Form. 3. Add a column to the Listview Column collection. 4. Set the Listview View property to Details. 5. Set the Listview FullRowSelect property to True. 6. Add the following code to the Form: Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load ListView1.Items.Add("aaa...

Help! Sharing Calendars
My boss and I both use Outlook 2000. She recently got a handheld computer, but does not have a USB connection on her PC. She wants me to access her calendar through network neighborhood and synchronize. How do I do this? ...

Help With Worksheet Operations
Is there any way in vba code to reproduce the same functionality in Excel as pressing Ctrl+right and/or Ctrl+down? I need a quick way to move to the last column and the last row on a spreadsheet. Also, is there a quick way to convert the worksheet to a string array or anything else (maybe a dataset)? Try Some Like this Sub MovetoEndDown() Range("A1").End(xlDown).Activate '(xlToRight) in case you want to move to the right. End Sub or Sub MoveDownPlus1Row() Dim NextRow As Variant NextRow = Range("A20000").End(xlUp).Row + 1 Cells(NextRow, 1).Activate End Sub Hope thi...

Formula to eliminate zeros
I have a column of values that is 200 rows tall. The values are eithe a five digit option number or a zero. (I can change the formula tha generates this column to put anything in place of the zeros of tha helps) I need to transfer those option numbers to Work Order in concise list without 175 to 200 zeros. I need to do this automaticall with a formula when the data is imported so my purchasing dept. doe not have to filter the numerous lists with each job with a drop dow menu. I could have all of the zeros on the bottom if I can get all o the five digit option numbers to appear at the top...

Formula for file location
Hi (I'm a newbie so please be patient) I am using excel to build a database for an online store, i have apro 3000 products and each product needs an image path (eg. D:/m docs/images/123.gif). I need a formula that will automatically insert the path to the image in its cell in the image colomn. To make this easier all the image names are the the part number[.gif so the image for product 123 is D:/my docs/images.123.gif. Can any one offer a formula to do this? Code ------------------- Example of spread sheet -------------------------------------------------- Make Model ...

array formulas-sumproduct and average
Hello, I need hel. I am using this formula =SUMPRODUCT(--(E2:E289=1),--(AVERAGE (H2:H289))) I don't know what's wrong with it. I need to find the average for column H in relation to 1(under 2 yrs) in Column E. Hope this makes sense. Thanks, Becky Try: =AVERAGE(IF(E2:E289=1,H2:H289)) Array-enter the formula with CTRL+SHIFT+ENTER -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Becky" <Becky@discussions.microsoft.com> wrote in message news:38BA387B-4DA3-4274-9CB5-CCC7E976DDAE@microsoft.com... > Hell...

Free & Busy Data messed up.. Plz Help!!
We have exchange v5.5. Recently one of the exchange admins was in the public folders messing around with permissions. He said he didn't change anything but something had to have changed because now all free and busy data, public address book, tasks, notes, etc are not accessible to new users. Any time we create a new email account they can't view there calendar (says it doesn't exist), nothing shows up in the global address list. Anytime they receive an email to accept a meeting it says unable to open item. I've done the usual things like reset the server. I loo...

Help with a simple query
Hi guys and gals, Can anyone help me with this query? I am having some issues with pulling in all names even though (it appears) I have the join correct. SELECT tblEmployee.EmpRptID, tblEmployee.UserName, Sum(tmpReports.NumOfSets) AS SumOfNumOfSets FROM tblEmployee LEFT JOIN tmpReports ON tblEmployee.UserName = tmpReports.UserName WHERE (((tblEmployee.IsCQATech)=True) AND ((tmpReports.CompleteDate) Between #2/8/2010# And #2/12/2010#)) GROUP BY tblEmployee.EmpRptID, tblEmployee.UserName; What am I doing wrong? You have defeated the left join by applying criteria to the right s...

help! Excel 2003 opens every doc on machine!
as soon as I start it, it starts opening every single file (even non-Excel files) & recursing directories. I have to kill it in the taskMgr or it will crash the machine. I can only start Excel in safe mode (with /s). How do I stop this? I have no idea how it started. the machine has current corporate AV/anti-malware. thanks let me add when starting in. safe mode, i get this: "Excel has detected that you have recovered files. These files will be opened the next time you starft Excel in normal mode." Try this first Tools>Options On the General tab you see &quo...

copying formula down
Using Excel 2003 Column A is dates. These dates plus 7 are being compared to the current date to determine whether "overdue" gets displayed in Column D. I'm using the following IF statement: If A1+7<today(),"overdue"," " When I copy the formula down, rows that have not yet had data entered into them are all displaying "overdue" because Column A is blank. Do I just need to remember to copy the formula down every time I enter a new row of data, or is there some way I can modify my IF statement to only do the calculation if there is data ...

Can I combine a Vlookup with an IF formula?
I'm not sure Excel can do what I want it to do - I have a VLookup formula on my spreadsheet which is returning data, however, I only want it displayed if it returns a specific condition. Is this possible ? -- CMB BT Hi, let' say you want to perform the vlookup if cell A1 is greater than 100, if not will leave the cell blank =if(A1>100,your formula,"") "Claire" wrote: > I'm not sure Excel can do what I want it to do - > > I have a VLookup formula on my spreadsheet which is returning data, however, > I only want it disp...

Where's my Help gone?
I use Excel Help frequently Earlier today it was OK but later, I tried and the "Contents and Index" in the Help Menu is greyed out. What have I done? How can I get access to Help Contents and Index? Any thoughts greatly appreciated Philip I don't see menus in the HELP panel of my Excel 2000, rather I see them as tabs not as menus.. Possibly you have an entry or an unanswered dialog box of some kind in Excel that needs attention. The main problem I have experienced with HELP is that the tabs don't show in help which can be expanded by clicking on the leftmost icon on ...

Urgent help: log issue
Hi all, windows 2003/exchange 2003 Sp2 Due to log space that is almost full, I use the eseutil /mk to check the checkpoint and moved some log files that are safe to move. Now, the log files are not trucated when we do a full backup. How should I fix this problem? Thank you. On Sat, 1 May 2010 06:41:01 -0700, ed <ed@discussions.microsoft.com> wrote: >Hi all, > >windows 2003/exchange 2003 Sp2 > >Due to log space that is almost full, I use the eseutil /mk to check the >checkpoint and moved some log files that are safe to move. Now, the log ...

WHY CAN I RECEIVE BUT NOT SEND IN OUTLOOK 2003 FROM A NEW DELL COMPUTER? pLEASE HELP
i USE AN OPTIPLEX 210, WITH VERIZON DSL AND MICROSOFT SMALL BUSINESS EDITION 2003, WITH OUTLOOK 2003. Norton Internet Security SUite, with lowered settings. DSL is not the problem I don't think. I can send out via an alternate gmail pop account and in Outlook Express. This doesn't happen every day, and usually when it does after 3-5 hours of use of my computer. I can still receive, but the SENT email gets stuck in my outbox. I have to shut down my computer and start it up again and then I can send email. I had some memory problems indicated at shutdown, but no code that is resear...

Addition Formula #3
What type of formula do I need to have a column total values in a row if the cells in the rows value is less then a value in a certain cell? Example Row 2 one has dates in it ( 10-1, 10-2, 10-3, & so on) Cell A1 has today’s date in it =TODAY () So I want Cell J3 to date the values in the cells A3 thru I3 of row 3 if the date above them in row 2 is less then Cell A1- today’s date. Thanks Chance ...

Array Formula Help -IF / Else clause?
Hi, I'm just getting to grips with Array formulae, and one of the formula I use most frequently is to count combinations of values over two arrays: For example: =SUM(IF(A1:A100="Ford",IF(B1:B100="Focus"1,0))) To give a count of all the Ford Focus in a 2 column list/array (Make & Model in columns A and B respectively) I understand how it works. Creates (and sums) an array of 1s for every combination where there is Ford & Focus. But what I don't understand is how the second IF fits in. Isn't the second IF basically the ELSE clause of the first IF...

If formula?
I am not sure if I need an IF formula but I am preparing a spreadsheet for work and basically it's a sponsorship form and if the person gets sponsorships of different levels they get a different prize i.e. Up to �10 - Wallet �10.01-�20 Bootbag �20.01-�30 T-shirt etc up to �60 and I would like to enter the amount in column B and then then let the computer to enter either the prize in column C. Can anyone tell me how to do this. I am totally new to excel. Thank you Wendy Hi One way is to use VLOOKUP. First create a table of information: 10 Wallet 20 Bootbag 30 T-Shirt 40 ....

Validation List (please help)
I need some step by step help. I have a workbook with three sheets. What I need is to type a customer name and ID# in cell G3 on Sheet1. I would like for this info to go to Sheet 3 cell A1. At cell G3 on sheet 1 I need a dropdown that will allow me to see all of the previous entries so I can select them when the same customer returns. (I would prefer to do this using a combobox from the forms list if I could so that I could make it fit without having to adjust the actual size of the entire column.) I also need for this list to be sorted alphabetically after each new entry is made. I foun...

please help with VBA for IM
Hello: I'm trying to use VB script here in Integration Manager 10.0 that pulls the lot number expiration date into my Inventory Transactions integration. I'm having an issue with the line that establishes my connection to the server. At first, it was giving me the "Datasource name not found" error. So, I put in the name of the server box. Now, it is giving me a "type mismatch" error. Below is my "After Document" VB code. Any help would be appreciated! 'Create a new connection Dim pConnection Set pConnection = CreateObject("ADODB.Connec...

Excel Chart colors help
How do I create the faded color effect like the tornado chart example in this link: http://peltiertech.com/Excel/Charts/tornadochart.html It's the orange and purple chart and the bottom of the page Thanks, Paula -- paulabrozek ------------------------------------------------------------------------ paulabrozek's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30377 View this thread: http://www.excelforum.com/showthread.php?threadid=552751 Hi, Double click one of the sets of bars. On the Pattern tab of the format dialog click Fill Effects. On the Gradi...

Calculating Dates by Month
I'm pretty new to this game, so here goes..... If I enter a date in cell A1, say 24/11/03.... I then want B1 to tel me what month that is (i.e November-03) But I also want excel to automatically complete cells C1-M1 with th next 11 months (December-03 to October-04). Thanks in advance!!!! : -- Message posted from http://www.ExcelForum.com Hi GreenMan! First your format problem Select A1 Format > Cells > Custom Format type mmmm-yy in the text box. Now copying across: Use the following in B1: =DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+2,0)))) Watch...