Formula Round Up The Values
Mar 3, 2007
I am using Excel as my payroll spreadsheet
A....name
B....rate of pay
C....date of work
D....date of work
E...date paid
F....# of hours worked
G....vacation hours
H....Gross Pay....B7*(F7+G7)
I....Net Pay....H7-J7-K7-L7-M7-N7
J....FICA....H7*6.2%
K....Federal....no formula
L....State.....no formula
M...Medicare....H7*1.45%
N....duductions....no formula
O....OT...no formula
P....Paid OT....O7*15.75
Q....Blank
R....FICA MATCH....J7*2
S....Medicare MATCH....M7*2 (ISN'T ROUNDING UP OR DOWN PROPERLY)
For Instance.......Joe worked 19.58 hours at $8.50 = $166.43 gross
federal is $0
state os $0
fica is $10.32
medicare is $2.41
R...FICA Match is $20.64
S...Medicare Match is $4.83.....(SHOULD BE $4.82)
Sorry but I didn't know how to properly explain the problem that I am having. The problem is in the rounding. Sometimes it doesn't round up and sometimes it doesn't round down.
View 9 Replies
ADVERTISEMENT
Feb 11, 2010
How do I get Excel to round up all values from .3 onwards. EG 2.36 to 3 and 2.29 to 2?
View 4 Replies
View Related
Sep 24, 2009
I am trying to do is have the roundup formula round up the result of a more complex formula BUT do it all inside of the same cell? The formula I have is in cell A1 and currently I have to have the cell that contains the round up formula (in cell A2) and have it reference A1. The complex formula is =((280283.47/798186.89)*(700*20*4)) and the result is -19,664.41 which I want to round up to $20,000. Is there a way to make this all occur in just cell A2 or am I stretching it?
View 4 Replies
View Related
Jul 8, 2008
I have value in one cell is 3.75 require formula or format through which it should show as 4.00
View 9 Replies
View Related
Mar 21, 2008
I am having a calculating issue with a formula I'm trying to use.
What I am trying to do is to multiply the " sum of one cell" with "another cell" for a total.
The following are the cells and data involved.
A12 = 93 C12 = $44.76
A19 = 93 C19 = =sum(C12*0.05) D19 = =sum(A19*C19)
The formulas looked pretty straight forward. My problem is the total I got in D19 was not what I calculated on a calculator. C19 is $2.24 and the above formula gave D19's total as $208.13 but the calculator's total is $208.32.
I can't figure out what is causing the different totals. If there is another formula I should use for this.
View 5 Replies
View Related
Jan 11, 2010
I am trying to use the following formula in Microsoft Excel 2007-
ROUND(IF(DataInput!$D$5 = "Black",0.81*E4*O4,(IF(DataInput!$D$5 = "Red",J4*0.025*E4*O4,(IF(DataInput!$D$5="Green",(57.00+IF(J4>25,(J4-50)*33.59,0))*O4))))),2
I want to use an IFERROR statement. If the results do not equal Black, Red or Green, I want the data to error so I can have the word FIX showing.
View 2 Replies
View Related
Feb 3, 2007
I would like to round this number (3.5) to become 4, but by using this formula =CEILING(A1,0.5) the result will be 3.5
i need a formula that turns 3.5 to 4
when i use this formula =ROUND(A2,0.5), 3.5 becomes 4, but 3.2 becomes 3 not 3.5
i.e. a formula turns 3.2 to 3.5 and 3.5 to 4
View 6 Replies
View Related
Oct 24, 2006
I don't know if there is a setting I'm missing or I'm going mad but when I use the round function in VBA it doesn't round.
I am using Excel 2000. See the example attached.
In the cell A2 I have a value 0.525, cell B2 has a formula "=round(A2,2)" which = 0.53, but cell C2 is assigned via VBA ie
Sheet1.Cells(2, 3).Value = Round(Sheet1.Cells(2, 1).Value, 2)
and the result is 0.52??
View 9 Replies
View Related
Aug 14, 2014
1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).
I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)
2. To include all unique string values except those starting with the letters "IC"
3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.
Here is the formula I currently use which serves #1 (above):
[Code] .....
Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.
Example of desired results:
Column A | Column B
AA | AA
DD | CC
AA | DD
CC |
DD |
DD |
IC |
IC |
View 14 Replies
View Related
Dec 23, 2011
Is there a formula that would allow you to take the average of all values within a range but not count the zero values? I thought something like this might work but it's not. Neither one worked.
=AVERAGEIF($E$4:$E$34,">0")
=AVERAGEIF(E4:E34,">0")
View 9 Replies
View Related
Aug 21, 2008
Trying to find the sum of all cells in the array described in the formula that are equal to the values inside the quotations. I used this exact (as far as I can tell) formula to find the sum of values that were NOT equal to my quoted values and it worked just fine. Any ideas why formula 'A' will not work but formula 'B' does work? I have a feeling I'm missing something simple here!
Formula A - Does not work:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000="REJECTED"),--('Master Lead Sheet'!$N$2:$N$10000="CONDITIONED"),--('Master Lead Sheet'!$N$2:$N$10000="APPROVED"))
Formla B - Works:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000"No Answer"),--('Master Lead Sheet'!$N$2:$N$10000"Disconnected"),--('Master Lead Sheet'!$N$2:$N$10000"Wrong Number"),--('Master Lead Sheet'!$N$2:$N$10000"EMAILED"),--('Master Lead Sheet'!$N$2:$N$10000"needs to be emailed"),--('Master Lead Sheet'!$N$2:$N$10000"Refund"),--('Master Lead Sheet'!$N$2:$N$10000"REFUNDED"))
View 9 Replies
View Related
Sep 28, 2012
Using vba how to round off the value e.g.: The number 582.356894 has to be rounded off to 582.4 ....
View 1 Replies
View Related
May 12, 2009
i have a list of minutes in cells a1-a5 say 123 256 147 158 235 divided by 60 giving a total of 15.3 hours. i want the hours to round up if over the. 5 mark or round down if under .5 how would i get the desired result?
View 2 Replies
View Related
Nov 6, 2009
A cell value is calculated via a formula in vba. I want to round the result down to the nearest odd number or down to the nearest even number, depending on conditions in an other cell. The result is already an integer.
View 8 Replies
View Related
Jan 7, 2010
Tried the code below to change figures from countless decimal figures to two, however excel is not having any of it,
Range("d:o").Select
Application.WorksheetFunction.RoundDown (2)
View 9 Replies
View Related
May 5, 2014
Why is Excel not rounding up a percentage of 93.5 to 94?
View 6 Replies
View Related
Feb 3, 2014
rounding the numbers. I am working on a quote in which quantity is arrived by dividing the sell price by Total sell price. The condition is the result (quantity) should always be a whole number, I can achieve that by cell formatting but when the calculation is done using handheld calculator the results are different.
I need the result to be same if using excel or handheld device i.e quantity in whole number.
View 5 Replies
View Related
Dec 11, 2008
Hi, I have this issue:
1.67 -> DISPLAY 2 ROUNDED
1.67 + 1.67 = DISPLAY 3 ROUNDED
but I would like to know how to sum 1.67 as 2
WANTED -> SUM 2 AS A NUMBER AND NOT A ROUNDED
2+2 = 4
I've been trying various combinations, but currently no joy
View 10 Replies
View Related
Jan 11, 2009
I need to work out how long the batten has to be so the roof sheets fit evenly, the measurement has to start from 1460mm and go up in increments of 80mm eg 1540mm, 1620mm, 1700mm and so on.
But the number has be closest increment of 80mm over the shed width if this makes sense, the size of the battens for 2400 width shed would be 2420mm but i need this to work out for any width shed not just 2400.
View 3 Replies
View Related
Feb 20, 2009
I am using the rounding function in excel and its rounding to the 10's, is there a way to round to the nearest 5?
View 4 Replies
View Related
Aug 5, 2009
i want a cell to round itself. i have a form i'm filling out, based on a percentage of a dollar amount. when the formula calculates, it only shows the first 2 numbers past the dollar point. however, the cell still "knows" what the number is. I have several of these formulas on a spreadsheet, and the sum of them at the bottom of the page is NOT what would you would get adding up the numbers you see on the page, as it is calculating numbers you can't see.
View 5 Replies
View Related
Oct 7, 2009
I have a number say:
156.9679628 then I go to format to change it to 2 decimal then it would show
156.97 and then I c&p that ... I still get 156.96796284 how do i just get
156.97
View 2 Replies
View Related
Oct 23, 2009
I'm creating a spreadsheet to calculate materials with the following columns Cost/10% of Cost/Customer Cost/Qty/Total cost.
I understand that whilst showing rounded to 2 decimal places excel stores more than this in the cell. which then throws out the Total cost by a few pence.
My research leads me to believe I need to use the ROUND function but I'm unsure which cell to use it or how.
View 2 Replies
View Related
Jan 26, 2010
round to two numbers. i have this formula
View 3 Replies
View Related
Jan 27, 2010
How do I take a date value and round it off to a fixed month and day of the year following the year entered.
Ex: In cell A1 I have a date: 12/28/2009
I want to round off the date to January 1 of the next year. So I need a function that would return 01/01/2010 (jan 1 2010).
View 5 Replies
View Related
Feb 22, 2010
19.25581
1.01346
3.29376
0.25337
0.25337
1.26683
Total: 25.33660
If I use the Decrease Decimal on the total 25.33660, it becomes 25.34. How do I round all the sub numbers so that 25.34 is achieved.
View 14 Replies
View Related
Mar 8, 2013
I have a spread sheet with col's that I want to average their totals, but would like them to be rounded up or down.
View 3 Replies
View Related
Aug 14, 2006
I have this formula which works well, but the 3-len part needs to be
dependent on another cells content. Is it possible to have the formula
automatically accommodate for this?
Eg
=ROUND(I36-I37,3-LEN(INT(Q16)))
in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37=
0.007
I need the formula to say ok the minimum division size is in multiples
of Q16 (0.010) and change the 0.007 to either up or down depending on
the calculation.
This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc,
and the sum in i36-i37 could be anything also as it is a calculated
error figure. I need the formula to be able to adapt automatically to
the reference DP in Q16.
Namely the "3-" part in the formula needs to accomodate for the Q16
number format changing.
View 14 Replies
View Related
Apr 10, 2008
I have sales figures from each region and need to convert those to percentage. and then when i try to add up those percentage shown on screen in excel, sometimes they will add up to 100.1% or 99.9%. Then i will have to manualy adjust the one that excel didn't round up but larger comparable to other regions in the 2 dp.
View 13 Replies
View Related
Oct 8, 2008
When a new employee gets a uniform, after they write their first order, I have to make sure the cost of the unifrom gets deducted from their check. Payroll ends on Sunday every week. I want to use conditional formatting to highlight the cell when it's eligible for deduction/reimbursement. No matter what day they write the order, it won't be eligile until after Sunday. Because the day of the week will vary, what type of formula can I use to highlight the cell? My sale dates are in column "AC" and the reimbursement column is "AF".
View 4 Replies
View Related