sum a cell value over 75

Please help!! 
Excel beginner! I need to build a formula that will add any remaining value 
over 75 in a cell.

For example:
In cell A1 = 76.5
In cell A2 - I need a formula that will sum anything over 75 and enter 1.5 
into cell A2. 

Thanks in advance for everyone's help!!
0
beginner1 (12)
9/22/2006 3:34:01 AM
excel.newusers 15348 articles. 2 followers. Follow

7 Replies
611 Views

Similar Articles

[PageSpeed] 23

> For example:
> In cell A1 = 76.5
> In cell A2 - I need a formula that will sum anything over 75 and enter 1.5 
> into cell A2. 

Perhaps, as a start .. try in A2: =IF(A1>75,1.5,"")
A2 will return 1.5 if A1 contains a number exceeding 75, otherwise A2 will 
just appear empty ("") -- this is an assumed return which is not specified 
above (you didn't say what you want in A2 if A1 doesn't contain a number 
exceeding 75).
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Beginner" wrote:
> Please help!! 
> Excel beginner! I need to build a formula that will add any remaining value 
> over 75 in a cell.
> 
> For example:
> In cell A1 = 76.5
> In cell A2 - I need a formula that will sum anything over 75 and enter 1.5 
> into cell A2. 
> 
> Thanks in advance for everyone's help!!
0
demechanik (4694)
9/22/2006 4:41:01 AM
Max,=20

My interpretation is very different but then I have been wrong before. =
;) =20

I think 1.5 is a variable i.e. it will change with A1.  In this example, =
the difference between 76.5 and 75 is 1.5.  So, my formula in A2 will be =
=3DA1-75.  If A1 is <75, A2 will have a negative number.

Will stay tuned.

Epinn

"Max" <demechanik@yahoo.com> wrote in message =
news:E8395113-572E-427E-8FA4-4DA865EDE995@microsoft.com...
> For example:
> In cell A1 =3D 76.5
> In cell A2 - I need a formula that will sum anything over 75 and enter =
1.5=20
> into cell A2.=20

Perhaps, as a start .. try in A2: =3DIF(A1>75,1.5,"")
A2 will return 1.5 if A1 contains a number exceeding 75, otherwise A2 =
will=20
just appear empty ("") -- this is an assumed return which is not =
specified=20
above (you didn't say what you want in A2 if A1 doesn't contain a number =

exceeding 75).
--=20
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Beginner" wrote:
> Please help!!=20
> Excel beginner! I need to build a formula that will add any remaining =
value=20
> over 75 in a cell.
>=20
> For example:
> In cell A1 =3D 76.5
> In cell A2 - I need a formula that will sum anything over 75 and enter =
1.5=20
> into cell A2.=20
>=20
> Thanks in advance for everyone's help!!

0
someone6932 (228)
9/22/2006 6:01:17 AM
Perhaps a good point, was blind to this relation earlier <g>:
> I think 1.5 is a variable i.e. it will change with A1.  
> In this example, the difference between 76.5 and 75 is 1.5.

In that case, my revised hunch for the OP would be 
to try instead in A2: =IF(A1>75,A1-75,A1)
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Epinn" wrote:
> Max, 
> 
> My interpretation is very different but then I have been wrong before. ;)  
> 
> I think 1.5 is a variable i.e. it will change with A1.  In this example, the difference between 76.5 and 75 is 1.5.  So, my formula in A2 will be =A1-75.  If A1 is <75, A2 will have a negative number.
> 
> Will stay tuned.
> 
> Epinn
0
demechanik (4694)
9/22/2006 7:22:01 AM
Maybe

=MAX(A1-75,0)

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Beginner" <Beginner@discussions.microsoft.com> wrote in message
news:AB4949E8-964E-498C-A2E6-49CD11802B95@microsoft.com...
> Please help!!
> Excel beginner! I need to build a formula that will add any remaining
value
> over 75 in a cell.
>
> For example:
> In cell A1 = 76.5
> In cell A2 - I need a formula that will sum anything over 75 and enter 1.5
> into cell A2.
>
> Thanks in advance for everyone's help!!


0
bob.NGs1 (1661)
9/22/2006 9:11:13 AM
This has turned out to be a guessing game.  :)

A couple of phrases from the request caught my eyes:

"...add any remaining value over 75..." and "...sum anything over 75..." =


So, I think Bob's formula should take care of this.  If it is a negative =
number, make it zero.  This is great.  Bob, I didn't know that I could =
use MAX( ) this way.  The MS Excel Help didn't seem to talk about this.  =
Wish there is BP Excel Help as well when I press F1.  <bg>  Thank you, =
Bob.  I can always learn something from you, be it something as =
complicated as SUMPRODUCT ( ) or something as simple as MAX ( ).

Have a good weekend.

Epinn   =20

"Max" <demechanik@yahoo.com> wrote in message =
news:EC14772E-4941-417B-BB1E-73B8FDBAB285@microsoft.com...
Perhaps a good point, was blind to this relation earlier <g>:
> I think 1.5 is a variable i.e. it will change with A1. =20
> In this example, the difference between 76.5 and 75 is 1.5.

In that case, my revised hunch for the OP would be=20
to try instead in A2: =3DIF(A1>75,A1-75,A1)
--=20
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Epinn" wrote:
> Max,=20
>=20
> My interpretation is very different but then I have been wrong before. =
;) =20
>=20
> I think 1.5 is a variable i.e. it will change with A1.  In this =
example, the difference between 76.5 and 75 is 1.5.  So, my formula in =
A2 will be =3DA1-75.  If A1 is <75, A2 will have a negative number.
>=20
> Will stay tuned.
>=20
> Epinn

0
someone6932 (228)
9/22/2006 8:10:45 PM
This worked perfectly!! 

Thanks Max and Epinn for the help also!!

Yes the 1.5 is a variable and will change depending on the number of hours.


"Bob Phillips" wrote:

> Maybe
> 
> =MAX(A1-75,0)
> 
> -- 
>  HTH
> 
> Bob Phillips
> 
> (replace somewhere in email address with gmail if mailing direct)
> 
> "Beginner" <Beginner@discussions.microsoft.com> wrote in message
> news:AB4949E8-964E-498C-A2E6-49CD11802B95@microsoft.com...
> > Please help!!
> > Excel beginner! I need to build a formula that will add any remaining
> value
> > over 75 in a cell.
> >
> > For example:
> > In cell A1 = 76.5
> > In cell A2 - I need a formula that will sum anything over 75 and enter 1.5
> > into cell A2.
> >
> > Thanks in advance for everyone's help!!
> 
> 
> 
0
beginner1 (12)
9/24/2006 1:50:01 PM
It's good that you did feedback further (Thanks for that!), otherwise guess 
we'll never know what was it that you really wanted <g>.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"Beginner" <Beginner@discussions.microsoft.com> wrote in message 
news:BC86383B-E433-4967-BE1B-774AFEE1219A@microsoft.com...
> This worked perfectly!!
>
> Thanks Max and Epinn for the help also!!
>
> Yes the 1.5 is a variable and will change depending on the number of 
> hours. 


0
demechanik (4694)
9/25/2006 1:45:52 AM
Reply:

Similar Artilces:

how can I show repeating values in a chart?
I would like to show modes in the form of a pie chart but am not sure how. For example the number 73 comes up 3 times in a column on my spreadsheet, how can I show that compared to the number 50 which come up 2 times in the sheet? Thanks for the help Hi, You will need to compute those values using formula or a pivot table and then chart the results. Cheers Andy Cindy wrote: > I would like to show modes in the form of a pie chart but am not sure how. > For example the number 73 comes up 3 times in a column on my spreadsheet, how > can I show that compared to the number 50 whi...

Excel 2002
-------------- Is it only me then that thinks this feature is mad ? Ah well. As it happens I found out the if you hide your detailed data using the out-line feature instead of the auto-filter, then you "can" have your cake and eat it ( Ken ) ! Hiding the detail using the out-line doe not make subtotal evaluate only visible data. Cool ! -------------- All messages from thread Message 1 in thread From: tur13o (research@basingstoke.plus.com) Subject: Excel 2002 - subtotal function only calculates visible cells in an auto filtered range. Why ? ...

VBA?Macro Newbie Question//Last field in a column with a value.
I am a newbie to programming Excel. I Have a workbook which has a average about 40 worksheets. I keep individual attendance and payment for my Alcohol and Drug Treatment program on these worksheets. Th individual worksheets are linked to a master roster worksheet in different workbook. My question for today is how can I link the last payment in a column i an individual worksheet to the master roster so I can tell at a glanc when a person made their last payment? Also, how could I link th appropriate cells to the master without doing it manually every time enter a new client? Thanks ...

Return Value from cells which match criteria (complex)
I have a worksheet I want to compute mileage on. Right now we're doing everything manually, but I want to see if I can automate the process in Excel without resorting to a lot (if any) VB code. The formula needs to examine 4 columns for the start point, then 43 or so columns for the destination point. From there it can reference another sheet with the mileage between two points and insert the correct value. The logic looks something like this: "Look for data in array a and in array b then depending on which columns hold the data, lookup the corresponding result in ...

Assigning a cell address to a variable
I'm sure this is a simple question, but I've been searching and cannot seem to find the answer to it. I have a couple of loops that run that put data onto a spreadsheet, I want to before the loop runs assign a variable the address of the first cell and at the end assign a variable the address of the last cell. I am trying to use Set LastDay = Range(Cells(3, CalCount)) but this gives me an error. Any ideas or help I would love. Thanks James, This would set the variable LastDay to the address of the last used cell in column A and then create a named range from ...

select part of a cell value string
Hi, I have a column of text (postcodes/zipcodes) in the following formats L3*1MT or L12*3DE. What i want to do is have a second column that reads the first column value and removes the *data, leaving just the L3 or L12. How can i do this?? Thanks Rich Hi Rich, Try this =LEFT(A1,FIND("*",A1)-1) -- HTH Bob Phillips "rich_j_h" <rich_j_h@discussions.microsoft.com> wrote in message news:62683D1F-C68B-457E-B2CE-250E99A3A71C@microsoft.com... > Hi, > > I have a column of text (postcodes/zipcodes) in the following formats L3*1MT > or L12*3DE. What ...

Counting unique values #2
Hi there! I am trying to count the number of unique referrals for numerous agencies. Using the table below.... Agent....Ref 1..........1 1..........1 2..........1 2..........2 2..........2 3..........1 4..........1 4..........1 4..........2 4..........2 4..........3 ...the result should be... 1..........1 2..........2 3..........1 4..........3 How can this be done? I have over 7000 entries to process! I've tried filtering and Pivot Tables, both without success. Any pointers would be gratefully received! Many thanks, Juerg -- jurgmay ---------------------------------------------...

How to sum few items
Hi A B X 1 Y 1 X 2 Y 2 Y 4 X 5 I want to sum up those numbers in B column which contains Y in their opposite in A column. -- Little Master ------------------------------------------------------------------------ Little Master's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28227 View this thread: http://www.excelforum.com/showthread.php?threadid=479050 Use the SUMIF function eg =SUMIF(A1:A6,"=Y",B1:B6) =SUMIF(A1:A10,"Y",B1:B10) -- HTH RP (remove nothere from the email address if mailing direct) &q...

gather information from another sheet with one related value
I have a workbook with two worksheets. Each sheet is getting it's data from a different source. Sheet 3 was exported from access and sheet two is from another workbook. There is one value (per record) that relates the 2 sheets and I need to bring information from the related value row to the second sheet. I have: =MATCH(B3,Sheet3!A:A,0) In cell M3 of sheet 2, this has given me the row number of the record from sheet 3. Which is (10) then I: =ADDRESS(M3,2,1) In cell N3 of worksheet 2, this has given me the cell address. Which is (B$10$) How can I get the value from the cell addre...

Matching a List Containing Redundant Values
I have a set of numbers (auto mileage), located in a horizontal range named Rental_Totals. I have a corresponding set of addresses, located in a horizontal range named Rental_Addresses, matched to Rental_Totals. I need the two ranges automatically sorted vertically in ascending order of Rental_Totals. To do this, I located a post from Bob Phillips ( http://tinyurl.com/6k462 ) that almost* solves my problem, and I handled it like this: Under heading Auto Mileage, I drag this down a column starting at F4: = SMALL( Rental_Totals, ROW(A1) ) Under heading Rental Addresses, I drag this down a co...

pausing a macro to input cell contents
My first macro using XP, Excel 2003. During the Recording of a macro, what syntax/keystroke is required to 'pause' a macro so as to allow input of data into a cell address, and then re-initate continuance of the macro to its next step? I do not want to end or stop the macro, just pause it automatically to enter data. Pause for input........................ Sub getuserinput() 'some of your code goes here usrinput = InputBox("enter a number") Range("A1").Value = usrinput 'resume rest of code End Sub Or if you want to select a cell or ra...

Secondary Value within Bar
I am trying to set up a simple bar chart. Example: Total$ 2003, 2002, 2001. Within each of those bars I need to fill the bar up with another value. Example Total$ was 10000, but 5000 when towards another category. I need to show the total as 10000, but fill the bar up to 5000 with another design. Make sense? Pat - You could make a stacked bar (or column) chart. In these you do not plot the total, but all the constituents of the total. If you don't know them all, use 'Other' and use a formula to subtract all the known constituents from the total. So your chart would ha...

Problem with Pick List associated cell
Can anybody help me with this issue. I am trying to select an item from a pick list and be able to automatically copy the adjacent cell value to another cell. Example: This is the pick list PAPER HOLDER - LEVEL I (CHROME) $2.80 TOWEL BAR - 24" LEVEL I (CHROME) $2.80 TOWEL BAR - 18" LEVEL I (CHROME) $2.80 Once I choose the item to fill in another cell location, I want the adjacent price to automatically fill in another cell location as well. One idea, use a formula in a col adjacent to the DV pick list Let's say your pick list as posted is in B2 down You could have this ...

FUTA / SUTA Values are incorrect
I need to print FUTA reports but the numbers are incorrect. Does anyone know how to adjust the FUTA / SUTA values in Great Plains? If you're wanting to change the rates, go to Microsoft Dynamics GP>Tools>Setup>Payroll>Unemployment Tax. From here you can select the state(s) involved and change the values. -- Phil Camp Director of Information Hopkinsville Milling Company "Jay" wrote: > I need to print FUTA reports but the numbers are incorrect. Does > anyone know how to adjust the FUTA / SUTA values in Great Plains? > . > ...

text cells, crosstabs? drilling down?
I have a workbook with 100 identical format worksheets. Each worksheet is an educational course. There are 25 row labels down the left representing 25 different competencies we need to teach. There are about 12 column labels to indicate if the course helps teach the competency and how it is measured if it is taught (such as written exam, oral exam, lab practical, specific competency on patient care, etc.) The cells only have text data (many cells have no data whatsoever). Is there any way to take advantage of something like a crosstab for numerical data using excel? If excel c...

Counting cells with a value in them
We are running an "If" equation on a column to determine if an event i taking too long to do. The resulting equation will yield an "X" if i is taking to long and a " " if not. I would like to have an equatio calculate the number of "X" in the column. Any ideas? Chris Nelso -- chris ----------------------------------------------------------------------- chrisn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=650 View this thread: http://www.excelforum.com/showthread.php?threadid=31880 We had a brain cramp and one of th...

Click in cell w/ formula and get colors in referenced cells
Hi, When you click in a cell with a formula, Excel will then put color around the cells that are referenced. Somehow I turned that feature off. Now I do not get colors in th other cells. Where is that option to turn it back on???? I've looke and looked but I just can't find that option. Thanks for the help -- albea ----------------------------------------------------------------------- albean's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2875 View this thread: http://www.excelforum.com/showthread.php?threadid=48436 ...

Cell Formats (like StyleSheets)
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I cannot find the way to store some characteristics (font, size, ....) of cell-formatting to own formats (like stylesheets, for reusing them) <br> There are just 6 standards and I cannot add more. <br><br>Any ideas? Sure you can :-) Where are you trying? The easiest way is to format a cell the way you want the style configured then while the cell is selected go to Format> Style. Just type the name you want the style to have then click 'OK'. Alternatively, go to Format> S...

Cell Display Issue
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a spreadsheet that I created in Excel 2007 (windows) and am using in Excel 2008 Mac. I cannot get the cells the right width to display the numbers in all the cells. <br><br>When the column is too narrow, &quot;####&quot; is displayed as you would expect. But when I increase the column width to correctly display that cell, other cells in the same column that were displaying correctly now display &quot;# #####&quot;. <br><br>Increasing the column does not solve t...

Can you freeze a result in a cell?
Here's the dilemma: I have a list validation in H7. You can choose from 6 different text values. Below, in H8, I would like to respond "true" (or yes) if a certain 1 of the 6 text values is EVER selected, and then not be changed by anyone else. For instance, if the "flagged" value is "blue", and a user ever selects blue from the drop-down list in H7, can I create an argument that populates H8 with "True"? And keep it as true forever, even if you go and change H7 to another value. Hope this makes sense. Thanks sirs and madams! Jacob Hi! You *CO...

Locking a Cell after It's Value is Calculated.
I have One Cell whose value is constantly changing once a week. The results of that cell, I would like to have placed in differents cells, once a week. EX: In worksheet1 Input in cell WK3, resulting calculation ends in cell M3, then cell M3 is copied to worksheet2, A1 (=worksheet1M3) I got this! Input in cell WL3, resulting calculation ends in cell M3, then M3 is copied to worksheet2, A2: The problem is that cell A1 changes to the new number HELP! Can I lock cell A1 after the value is copied to it? Can I set conditions (time / date) on the cell after the value i copied to it so it won...

Counting colored cells?
Group, Does anyone know a way if I can do something like a SUMIF() based on the background color of cells? For example, if I have data and I color the cells in column individually, and I want to sum the contents of all the green cells, can I do that? Furthermore, can I do a COUNTIF() to count the number of cells with a given color? Thanks, Mike If cells are colored due to Conditional Format then use the CF criteria to count. If colored manually, you must use a VBA Function. See Chip Pearson's site for this. http://www.cpearson.com/excel/colors.htm Gord Dibben MS Excel MVP On 1...

IF SUM #3
Hi need need a foulmles for the following, Total Sales Total Hrs Man Hours Prod Bonus SPH Paul Smith 4 7.25 8 91% £16 0.55 Total Sales Total Hrs Man Hours Prod Bonus SPH Paul Smith 4 7 8 88% £8 0.55 THIS IS A TABLE WHERE IT GET IN INFORT Ratio Productivity Bonus 0.35 - 0.4 90% 2.00 0.41 - 0.5 ...

Dlookup minimum value
Hello. I'm trying to use Dlookup to get the minimum date from a table. I was trying to do the following command: mDate=Dlookup(min("dateField"),"tblName") Somehow the code points an error on"Min", saying that "Sub or Function not defined". Is it possible what i'm doing? Is there any other way instead of looping through all the records? Thanks. Luis Try DMin() instead of DLookup() -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rat...

Calculations not working on pasted values from Access
I'm using Office 2000 on W2k, and when I copy and paste a column of numbers from MS Access to MS Excel, I am unable to perform a Sum function on the pasted values. The function always returns zero. If I then type in the values directly it does work. Anyone know why? Is this caused by a virus and is there a patch? It is because it is seen as text, try to copy an empty cell, select the imported numbers, do edit>paste special and select add. -- Regards, Peo Sjoblom "Scott Sullivan" <anonymous@discussions.microsoft.com> wrote in message news:0c0001c3a22c$790cd250...