Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Excel Forum

Minus A Value If Sum Is Greater Than

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

What formula would I use if the total sum of a column was greater than 4.00 then I would minus 0.35. If not minus nothing.

Thankyou

View Answers     

Similar Excel Tutorials

How to Use the IF Function in Excel
The IF statement is a simple yet powerful tool. Today we will go through how the IF function works and some useful ...
Average Non-Contiguous Cells in Excel
How to average non-contiguous cells (cells that aren't next to each other) if those cells contain numbers and are ...
VBA Comparison Operators
VBA comparison operators are used to compare values in VBA and Macros for Excel. List of VBA Comparison Operators C ...
Logical Comparison Operators in Excel - How to Compare Things
Logical comparison operators allow you to compare values in Excel to see if they are equal, not equal, greater than ...
Calculate the Total Time Worked Minus Lunch Breaks in Excel
Ill show you how to use Excel to calculate the total time worked in a day minus lunch time or any breaks. This is ...
Calculate Someone's Age in Excel
How to automatically calculate someone's age using Excel.  This method is simple and will update every year so tha ...

Helpful Excel Macros

Sort Data that Doesn't Have Headers in Descending Order in Excel
- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Highlight the Column of the Selected Cell
- This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back

Similar Topics







There sure is...Here's how:

From the Excel main menu:
<data><group and outline><settings>
Uncheck: Summary rows below detail

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"JMSprout" wrote:

> When you highlight rows, go to Data, then Group or Outline, then Group, it
> creates a nice plus/minus expand/collapse box for that group, only when the
> group is expanded the button to collapse it is at the bottom row of the
> group. Is there a way to get this button located at the top row of the group?



Hi,
I made a simple worksheet that we have been manually entering the time for employees, but there are too many errors (even with simple math). Can someone help me convert the time of ex: written 8-4:30 or 10-3 (meaning 8:00am to 4:30pm) where you have the total weekly hours? Right now I have a column for overtime. Is there a way to automatically calculate the over time also? The work day is 8-4:30 with a half hour lunch (lunch is not calculated into the hours, so you minus a half hour). 8-4:30 equals 8 hours. So, if a person works 8am to 6:30pm, how can I set it up where in the first row of time, I will type in 8-6:30pm. In the totals column to the far right, it will display 8 hours. In the (O.T.) column, it will display 2 hours.

This way, when I call in my payroll, I will have the total "regular" hours in one row and underneath, I will have the total overtime hours.

Or... does anyone have a better solution to keeping track of their hours?

Thanks!
Marty


Hi, all - I have been banging my head on the desk over this one...it's gotta be so simple, right? Have searched online, and on Mr. Excel, but still can't get this right.

I need a formula in cell L3 that says "IF cell K3 is greater than 25 but less than 30, K3, otherwise, blank." Cell K3 currently contains the value 30. My formula in L3 currently is =IF(K3>25,K3,IF(K3

Hi there

Please can someone help me. How do I create an IF formula to find cells where the number of hours in one cell is more than the number of hours in another cell? eg one cell has 0.40 hrs and another has 02.25 hours - I want have an IF formula to say where 02.25hrs > 0.40 then type "FAILED" but I cant overcome the greater than problem on the hours which is giving me the wrong answer.


Hello,

I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:

I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.

I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?

If somebody could help me out that would be great.

Thanks,
Randy


I'm attempting to filter columns E, G, and I all at the same time. I need to
display rows that have values greater than 0 in any one of those three
columns. So far I can only filter progressively. If I filter column E and
then filter column G, the second filter is only applied to the results of the
first filter.

Does anybody know if it's even possible to do what I'm trying to do?



How do I calculate the number of batches per hour? Here's what I have so far:

Cell B5: start time: 4:15 formatted as 4:15:00 am
Cell B6: finish time: 6:15 formatted as 6:15:00 am
Cell B7: total time: 2:00 formula in cell: =TEXT(B6-B5,"h:mm")
Cell B8: total batches processed: 22 (this is entered manually)
Cell B9: batches per hour: formula in cell: =B8/TEXT(B7,"h")

as long as I have this formula in cell B9 the answer comes out correct which should be 11 per hour.
If the formula in B9 is B8/B7, the answer is 264.0, Is this because of the way excel is reading the total time or the total time is formatted as a time, not an actual number? Is this the correct way to solve the problem?
answer should be 11 per hour.


I am trying to calculate the average Talk Time into Minutes and Seconds. Every time I have tried, I get the wrong average.

For example, if I have a total of 53 calls and the total talk time is 4:19:05, what would the average talk per call be? I tried doing everything in seconds (this would give me a total of 15545 seconds) but the total I was getting is 4:89.

What would the formula be to get the correct average time?


I'm trying to figure out what the formula is to multiply the total hours worked per week by their hourly pay for each individual. kind of like the
=Sum(I25:I32) Formula but I want to multiply the outcome of that formula by the hourly rate of 11.00, 15.00 and 16.00. Does anybody know how this can be done? If so I would greatly appreciate it.
Thanks!
charitydc


Good afternoon!

I'm working with Excel 2003 sp 3 on Windows xp.

I have been banging my head trying to jar loose the formula to calculate man days where 1 equals 8 hours. I have one that was given to me by a coworker:

=INT(Q3/8)+(((Q3/8)-(INT(Q3/8)))*0.8)

That calculates man days from total hours. I need a formula that will total man days and return the man day answer. Example:

weekending 12/25 = 16.2 which is 16 man days and 2 hours
weekending 1/1 = 24.7 which is 24 man days and 7 hours

Total = 41 man days and 1 hour

I have also been asked if there was a way for a number to be entered into a spreadsheet and automatically put in order without a macro or "pushing a button".

Any advice is greatly appreciated!

Happy New Year!

~ Brenda ~


Hi

I'm not sure how to do this, but I want the Total (column D) to be the sum of the Unit Price (column C) times the Quantity (column A). Is there a formula you can apply to a column or do I have to do it in individaul cells?


I am trying to copy balances from one worksheet to another over a total of 12
worsheets ..... this is for petty cash monthly sheets and I need a running
total over the 12 sheets.

At present I have to do this manually .... is there a formula that I can use
that will automatically carry the balances over

Thanks for your help



I'm trying to come up with a solution to calculate the man hours and days worked over multiple sheets.

I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.

The days of the week consists of "Name" "Date" Hours"

The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"

Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.

I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.

I have attached a sample file.

Thanks in advance for your help.


Sorry for the question. Normally I find answers to my excel questions by going through the help tab or by searching on Google. However, I don't even know what question to ask on this one!?!

Basically I have created a spreadsheet with several columns, but I have one column that lists the shirt size (YS, YM, YL, AS, AM, AL, XL, 2X, 3X) of each person. Is there a formula that I can create that will tabulate the number of sizes (i.e. AS=2, AM=7, etc.)?

In previous years I made a column for each size, and simply placed a "1" in the correct column, and had excel just add the 1's from each column. However, that takes more time and space. I was hoping to streamline it this time around.

Thanks for taking the time to read this post. Any help would be appreciated! Thanks, doug


hi guys,

i have a simple spreadsheet where i'm totalling a few values thru a formula. it's similar to this:

=D13*F13

i'm trying to create a funcion that says if d13 * f13 >= 300, i want the value to stay at 300 max and at the same time, if d13 * f13 < 300, i want the total to remain whatever it is.

i have been able to create both of these formulas successfully by themselves but i can't combine them. is this possible?

any help would be greatly appreciated!!


Entering time values in custom format [h]:mm:ss
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell - then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).




Is it possible to have numbers added to the same cell and have excel continue
to calculate the addition for me in that same cell......ex: I have the number
8 in cell d2 and I want to add the number 8 to that cell and have excel add
the 8 to the previous 8 for a total of 16 in the same cell.....the next time
I would add 5, and the total would be 21? Can this be done in a single cell?



Today I ran into an odd problem. I typed in values for column A rows 1 through 10 then values for column B rows 1 through 10. then in column C, I made the formula C1=A1/B1. The math was correct it showed 542 in the C1 cell. So I dragged that formula down and it showed 542 in all column C cells which is not correct. And when I went to check to see if the formula was correctly dragged it was. For instance, the formula in C2 is =A2/B2 however the value of that cell showed 542 which was not the correct math/value.

But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from.

This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening.

Thanks for any tips on this.


Column 1 has roughly 20 rows of information. Cell C1 has the formula =A1.

Is there a formula so that when I drag C1 horizontally into D1, E1, F1, ..., the values placed in each cell will be =A2, =A3, =A4, ...

I do not want to transpose the values from column 1 into C1, D1,.... I want these cells to have a formula that links them up to column 1's values

Thanks

Hi there,

I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys!

In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2:

=Sheet1!A3 (or whichever cell it is)

That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. without a fixed range, as new rows are going to be added to the column).

If anyone could point me in the right direction, I'd be very very grateful. Thanks.


I am trying to sum all numbers <0 using the SUMIF formula in Excel 2003.

FORMULA: =sumif(range,criteria,sum_range)

I got it to work, but I have cell ranges to total that are not next to each
other (such as A5:A15 and C5:C15).
When selecting the ranges for the formula, Excel writes them A5:A15,C5:C15

because of the comma (,) between the cell ranges Excel is reading the A5:A15
as the range and C5:C15 as the critera. How can I get this formula to allow
me to select multiple cell ranges?

All help is appreciated.

Thank you.



Hi All,

If I drag a formula to the right it adapts the column letter in the formula from A to column B, which is standard Excel functionality.

However, if I drag the formula to the right I want Excel to skip a column each time.

In other words:

=A1 ; = B1 ; = C1

Should be

=A1 ; = C1 ; = E1 etc...

Is there an easy trick to do this?

Thanks,

Michiel


Dear Sir,

If any one could give me a solution for this its will be a great help for me.

I am working in a bank and on every day we receive Month to date data of Loans and advances made by every branch with Region wise total and manager wise total. With Sum of loan and count of loan. In our MIS format the in A coulum branches are sorted and listed in a sequence with sub total Region. I have data where if if do the pivot and change the data the out put should be displayed accordingly in the MIS format. Can any one help me how can we do this or is there any other office addins/softwares are available if so the data is changed on a daily basis and if we do the pivot it will automatically copy the data to the MIS format.

Thanks.


I'm trying to format a worksheet to show how many days overdue certain tasks are, and running into a couple of issues. The sheet is set up with Due Dates in Column A, Days Overdue in Column B, and Date Completed in Column C. My first thought was just to use as simple a formula as possible in Column B =Today()-A2. This worked alright for tasks that already had Due Dates assigned, but if Column A was blank I'd get a high number in Column B. I also realized that tasks will continue to show as overdue even after I enter the completed-on date in Column C. Is there a relatively simple way to tackle this formula? Thanks in advance for suggestions. (I've attached an example with the column headings I want to use.)


Hello everyone, I am new to this forum and have been using excel for about 3 years. I have learned alot but still can not figure out how to make numbers add up in one box like a running total. Meaning, If I have a list and in the list is one cell for each item say like cell a1 is for a can of coke and cell a2 is for a car tire and so on,and i want to keep a running total by adding a number to cell a1 or a2 like say "3 i.e. 3 tires or cans of coke" and later that day i come back and need to add 2 more ......so i want to be able to just click on the cell a1 or a2 and enter the number 2 and the cell would add the number in it to the number im adding to it to show a result of 5 tires or whatever. I am sorry If i am not asking the question in an understandable manor but its the best way I can think to ask it.lol anyways, any help would be appreciated, Thanks in advance.


James