Convert Data With ROUNDUP And ROUNDDOWN Functionality Using VBA

Jan 8, 2014

I've got a data dump that must be edited/converted prior to pushing it into a different main frame system. There can be NO decimals in the end result. Here's the challenge:

______Col A____Col B___Col C___Col D____Col E
Row1__TOT_____1Q_______2Q_____3Q______4Q_____
Row2__20.00____5.00_____5.00____5.00____5.00___
Row3__26.00____6.50_____6.50____6.50____6.50___
Row4___0.00____0.00_____0.00____0.00____0.00___
Row5_139.00___34.75____34.75___34.75___34.75___
Row6__13.00____3.25_____3.25____3.25____3.25___

Starting on Row 2, there's a break down of 4 quarters of data divisible by 4 using the whole number TOTAL of Parts in Col A.

The analyst must first break it down into 4 parts (as done in Col B through E). Then, round up and down appropriately to ensure that NO DECIMAL data is entered into another main frame system.

Can't copy one formula downward because if it ends in .50, the up/down sequence is diff from how it would be rounded if it ended with .75.

Need vba to be smart enough to evaluate "IF" and apply "THEN" according to what it sees in Col B's data and convert B, C, D and E appropriately. (I will tie this to a button within a toolbar to run the macro/vba when the conversion of B thru E is needed.

EXAMPLE:
IF B's data ends in: xx.00 or xx.50, then convert those 4 quarter's data accordingly:
ROUNDUP, ROUNDdown, ROUNDUP, ROUNDdown, (this one doesnt really matter but works)

IF B's data ends: xx.25, then convert those 4 quarter's data accordingly:
ROUNDUP, ROUNDdown, ROUNDdown, ROUNDdown,

IF B's data ends: xx.75, then convert those 4 quarter's data accordingly:
ROUNDUP, ROUNDUP, ROUNDUP, ROUNDdown,

Using ROW 6 as an example for how to handle xx.25's:
B6 would convert up to 4
C6 would convert down to 3
D6 would convert down to 3
E6 would convert down to 3
TOTAL after conversion = 13

View 9 Replies


ADVERTISEMENT

Keep Functionality When Adding New Columns Of Data?

Jun 26, 2013

Each week I get new weekly data-metrics. I have a series of formulas that figure LAST 4 weeks average, LAST 8 weeks, etc etc. How do I drop in a new week of data and have my formulas adjust? For instance, my formula figures last 4 weeks average of May Wk 1, May Wk 2, May Wk3, May Wk4. Now it is June Wk1, so I want to figure (without changing any formula) May Wk 2, May Wk3, May Wk 4, June Wk 1.

View 1 Replies View Related

Textboxes, Data Arrays And Vlookup Functionality

Nov 20, 2008

I would like to select a item from a list and have a text box display data from the next column (corresponding row) Also, I would love if I could type something into a second textbox and have that copy onto a third column (again, corresponding row) Also, if the add comment command button could transfer that record to the "commentted items' sheet. I have attached an example.

View 2 Replies View Related

ROUNDDOWN Function

Jul 27, 2007

I have a cell that is called "Number of containers", this cell affects my "Freight costs" and several other cells. I want this cell to never contain decimals, I have used the format menu and changed the number of decimals shown but that does not affect the number itself. It still uses decimals when calculated. I want this cell to use no decimals and also to always be rounded off downwards (since there can never be more than a certain number of items in a container). Can I use the ROUNDDOWN function?

View 9 Replies View Related

Using RoundDown Function In A Macro

Jul 27, 2009

I have formula as follows that I am trying to use in a Macro. It is working fine as a function by itself.

View 5 Replies View Related

Roundup Value In Cell

Apr 24, 2014

How to roundup the value in B2 cell. I provide you the manual output.

View 3 Replies View Related

Get Average Then Roundup The Value

Apr 20, 2014

i m using this to get average

r4/s4 = result(t4)
r4 = 5
s4 = 517
Result(t4) = 517/5 = 103.4

now i use the roundup function to get the decimal less value

=Roundup(t4,0) = Result = 103

i want both function in one function to get result

View 2 Replies View Related

Roundup To Nearest 50?

Apr 29, 2013

I need a formula that will roundup the amount in a cell to the nearest 50 (as in 246 £ will become 250 £)

View 4 Replies View Related

Roundup Function

May 24, 2007

I am trying to get excel to roundup to the nearest 100 any numbers that are in 3 columns. Each row in these columns are then added together for a total sum of that row. How can I get all the numbers in these three rows to be rounded up to the nearest 100 after entry?

View 3 Replies View Related

SUMIF Roundup The Values

Oct 6, 2008

I'm using this formula below but it's not rounding up correctly.

=SUMIF('[Bill''s Angels Bowling.xlsx]Variables'!$D$8:$D$71,V3,'[Bill''s Angels Bowling.xlsx]Variables'!$I$8:$I$71)

The list of numbers is follow
45.815625
34.509375
30.7125
21.009375

Should be as follow
46
35
31
21

Total should be 133 I'm getting 132

View 4 Replies View Related

Combining MAX, ROUNDUP & IF For The Same Cell

Feb 22, 2010

I am trying to create one formula (in one cell) so that it performs the following three things:

1) Rounds any number to the nearest dollar

2) Makes the minimum dollar value $2.00

3) If another cell (G2) reaches $100.00 or more, I want it to revert to another cell's (B2) value.

So, in essence, I want to combine the following two things:

=MAX(2,ROUNDUP(F2,0))
AND
=IF(G2>="100",B2)

I do not know how to correctly order these so that it performs all three things I'm looking for it to do. I've posted this before, but was given an answer that created a circular reference and did not make the minimum value $2.00.

View 7 Replies View Related

Vlookup And Roundup Return #n/a

Jun 11, 2009

Which returns 0.03316

When I look at the formulas in B17 by clicking on the name of the fuction and then clicking on the function button, the roundup function arguments shows 0.071. Also the vlookup function arguments shows 0.071. Yet I get the #n/a as the result from the vlookup in B17.

I have tried coercing the formula by addin +0 like =VLOOKUP(ROUNDUP(B15,3)+0,$N$2:$O$91,2,FALSE)

I have also tired *0 like =VLOOKUP(ROUNDUP(B15,3)*0,$N$2:$O$91,2,FALSE)

Here is a kicker... When I replace B14 with 0.9 or 0.7 or 0.6, the formula works. but I get a #N/A on 0.5.

View 9 Replies View Related

Roundup Formula Results

Oct 26, 2006

Is there any way that I can apply the roundup feature to an entire workbook without having to do each cell individually?

View 9 Replies View Related

Roundup Function - Two Decimal Places

Mar 13, 2014

I'm on Excel 2010 and I need to round up a complete price list to a 0.90 decimal.

Here is the formula that I have in my cell : =((J5-(J5*N$2))*M5) I need to round the result to XXX.90

View 4 Replies View Related

Correct Syntax Of ROUNDUP / ROUNDOWN

Jan 8, 2007

A UDF function seems to interfere sometimes with the rest of the workbooks entire process for some reason.

I could use the CEILNG function to work around the glitch, but it does not seem to allow the flexibility required because it takes everything to 1. So for example if a Value is 9.24, CEILING will Sum to 10 when when it must be 9, unless I'm missing something.
But if the Value is 9.25 or 9.26 then rounded value must go to 10

The basic math required in the formula bar then is: ...

View 9 Replies View Related

RoundUp Vlookup Result If Greater Than X

Jan 25, 2008

I have a formula that calculates a revenue per day by taking total revenue devided by total days, from that I have another cell doing a VLOOKUP on that to find what tier that value falls under almost like a grade book, however I need the division to be rounded up if greater than x.xx5 because when I do the VLOOKUP sometimes it drops the product to the lower tier when I needed it rounded up. example:

=IF(ISERROR(Data!H6/Data!E6),"",(Data!H6/Data!E6))

if the value of those were to actually equal 1.256 I want to be able to have the VLOOKUP return the tier for 1.26 not 1.25

View 7 Replies View Related

Excel 2003 :: ROUNDUP Of Result In Cell That Contains Formula?

Jul 8, 2014

I'm having the following problem:

A1 & B1 = variable inputs from a calculator

Value C1 is calculated as follows: IF(A1<=0,"",(B1*G1))................(where G1 is a fixed value with 6 digits)

MY PROBLEM: The result in C1 should always be a ROUNDUP value.

I just don't know how to combine ROUNDUP with IF(A1<=0,"",(B1*G1))

View 4 Replies View Related

How To Use SQL Decode Functionality

Jun 11, 2013

I need to use SQL decode functionality in Excel. I tried but Excel won't accept decode. Is there a way to get the same effect in Excel?

Example: quite often, people use letters to represent numbers, like 1234D = 123.44, 5432G=543.27 in text files. To converter from letters to numbers, I can use decode (substr(A1,5,1), 'A', 1, 'B',2,'C',3,......,'{', 0, null).

Not sure how Excel handles this?

View 4 Replies View Related

Search Functionality Within Userform

Mar 27, 2014

I have a standard multi-page excel workbook in Excel 2013. I have built a user form that will allow for user input into my main excel page. This inpu includes many different variables, including one called "Paint". The value of paint can currently be selected from a drop down box that pulls its values from a spreadsheet in the workbook (Paint!A$1,A$50) but here's the issue. I have over 50 possible values for paint and it makes the drop box time searching for the right one kinda hard.

Here's what I want to do: Have a button beside the paint drop-down that says "Search", When the users clicks it it opens a search dialogue of some kind that will return the closest values to their search. When they click the value they want it gets entered as the selected value for the "Paint" drop-down box.

Here's my questions:
-Is this even possible ?I know enough about VBA to know that this is probably difficult in which case id still like to try.
-If it is possible. Are there perhaps any places you could point me to that would have some sort of starting point for me to work from. Remember I got the other part already I just need the search functionality.

View 3 Replies View Related

Dynamic Functionality In Workbook

Mar 8, 2014

I have sheet1 along with three columns as below

Result column will show the result after sum ** column A & B

[Code] ......

This is simple that i will add this formula =A2+B2 in Result column cell C2.

But i want to perform this addition action on Sheet2 and return the result in column C2 ** Sheet1.

What formula should be populate in Result column ** Sheet1 that could catch addition formula from Sheet2 dynamically and display the result ** column A & B in Sheet1 .

And what type ** formula should be use in Sheet2 to add two columns dynamically.

The same concept that we are using in any functions like as below:

Sub Function Plus (value1 As Double, value2 As Double)
Plus = (value1 + value)
End Sub

View 3 Replies View Related

Edit: Countif Functionality

Apr 29, 2009

I have a column of numbers, for simplcity lets say they are 1,2,3,4,5. The numbers change automatically, so the column can look like 1,1,1,1,1 or 1,5,3,3,2 etc. I would like to know which command can monitor this column and return a true statement if two or more of those numbers match the number I specify.

View 3 Replies View Related

Searching For ADD-IN's To Enhance The Functionality..

Oct 8, 2009

One of the useful functionality was the Excel-Addins , now I would be glad if someone could help me by posting the latest Addin or Analysis tool-pack. A setup file would be of great help as some of my formulae are suing such functions which ach are not provided by the reguale EXCEL such as "QUOTIENT" and "NETWORKDAYS"..

There's just one more request , I had a very good font by the name "POOR RICHARD" in one of my files, however after formatting and re-installning I do not have it anymore.

View 5 Replies View Related

To Include Search Functionality In Listbox

Jan 27, 2014

By typing either first letter or first two letters in cell A2 the list box should bring up matching street names. Also listbox should manually allow to select required street name by scrolling down.

View 3 Replies View Related

Porting Hyperlink Functionality From One Cell To Another

Jul 9, 2014

I have a hyperlink in a cell that is put there by copying and pasting it from an e-mail message. The hyperlink has a nice friendly name that is not the actual URL. Let's say that the hyperlink is in Sheet 1 A1.

In Sheet 2 I have a cell that says C3 = HYPERLINK('Sheet1'!A1), however this doesn't import the URL with it. All it does is make a hyperlink with the nice friendly name that goes nowhere.

How can I reference Sheet 1 A1 and have the result in Sheet 2 C3 be a clickable URL that works exactly like the original that is sitting in Sheet 1 A1?

I have tried the following code :

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count > 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula

[Code]...

However it does not work for me. All it does is deliver NAME# as a hyperlink which cannot be clicked. I am very new to putting code into Excel so it is possible that I do not understand how to make a user-defined function work. I don't even understand how the code itself works.

View 5 Replies View Related

Count Functionality In XL Macro Or Formula

Aug 11, 2009

I am looking for a solution by a macro or a formula to get the number of occurance of a particular word in all the worksheets of a xls.

View 4 Replies View Related

Merged Cell - Lost Functionality

Oct 5, 2011

I feel like I have lost some functionality due to merged cells. For a worksheet change event I have the following:

ElseIf Target.Value = "" Then ... blah blah blah

For a normal cell, every time a cell is deleted (in the appropriate target zone) then it runs the code I have in the subsequent lines

Except it doesn't work for merged cells, unless I manually go into the cell, backspace all of its contents and then press enter

View 9 Replies View Related

Change Functionality Of Tab Button In Excel?

Apr 27, 2012

Is it possible that if i press tab in excel my active cell offsets by a row and two negative columns.

View 1 Replies View Related

Can't Find Simple Excel Functionality

Nov 8, 2012

I'm looking for the functionality in Excel where you can unhide/hide a group of columns or rows by clicking the plus or minus sign below the ribbon but above the spreadsheet itself. I forget how to do it, but it basically groups the columns together and hides or unhides all of them with one click.

View 3 Replies View Related

Insert The Rounddown Formula Into This Formula

Jan 29, 2008

I use this formula for converting date of birth to a number based on today’s date. I have tried to have the number.. 6yrs 6 months to print only 6 not 7 years old. How do I insert the rounddown formula into this formula

=IF((AD37=""),"",YEAR(AQ36)-YEAR(AD37))

View 9 Replies View Related

Protect Worksheet Without Affecting Macro Functionality

Oct 15, 2008

I've attached an excel invoice template I've been making for our company. I'd like to protect all the cells except A12:H42, C8, B9, G4 and I44. The problem is, if I protect the worksheet (with these cells unlocked) then it won't let me run the macro (for inserting new rows for extra products).

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved