Complex SUM With ROUNDing - All In One Cell Possible?

Feb 25, 2009

I am an experienced Excel user, but it does not seem possible for this formula to be written all within one single cell. The SUM combines rounding a known value, followed by calculations, and subtracting a rounded number with modified calculations.

This is the formula which does work, although I would prefer for the sake of accuracy (and succeeding in the task of making the formula do exactly what I wanted it to do!!):

=ROUND(SUM(T9/1.5)/5,0)*(5)*(6.5%)+(26.25+15.81+1)+SUM((T9/1.5)*(6.5%)+26.25+15.81+1)*19%

The value of T9 is 2283.3 recurring.


There are two main parts to the SUM:

=ROUND(SUM(T9/1.5)/5,0)*(5)*(6.5%)+(26.25+15.81+1)

...and:

+SUM((T9/1.5)*(6.5%)+26.25+15.81+1)*19%


The challenge is to insert another:

=ROUND(SUM(T9/1.5)/5,0)*(5)

in replacement of:

=SUM((T9/1.5)

I'm aware that the use of brackets differs between the two sections of the sum, but it is neccessary to allow the correct answer.

Is this too much for one poor cell to handle?

View 8 Replies


ADVERTISEMENT

Banker's Rounding Or Scientific Rounding

Jul 9, 2008

I am trying to round similar to Banker's Rounding or Scientific Rounding but I can't find a consistent formula that works perfect with decimals.

Using three decimal places for all the samples, I can get 0.0785 to round to 0.078 but 0.1785 wants to round to 0.179 instead of staying 0.078. Or 0.0005 will round to 0 but 0.5115 wants to round to 0.511 instead of 0.512.

Here is a list of sample numbers along with desired results:
.0785 should be .078
.5115 should be .512
.5035 should be .504
.0005 should be 0
.0025 should be .002
.0194 should be .019
.0195 should be .02
.0135 should be .014
.0115 should be .012
.8115 should be .812

I cannot find a formula which gives me all of these results. Here is a list of the formulas I have tried so far (NOTE: cell A2 is the working cell in my worksheet where I enter the number to be rounded)

1) =MROUND(A2,0.001)

3) =ROUND(A2,3)

4) =IF(ISERROR(IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOOR(A2,0.001))),0,IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOO R(A2,0.001)))

5) =EVEN(A2)

6) =ROUNDUP(A2,3)

7) =ROUNDDOWN(A2,3)

View 9 Replies View Related

Rounding A Number Up In A Cell

Jan 28, 2009

I need to round up the answer of 1.72 to 1.73 for the formula listed. The formula in that cell reads as follows: =(22/12.762)

View 2 Replies View Related

Rounding Up Cell To Closest 1000 But Also If Cell Is Less Than 0 To Show 0?

Jan 8, 2014

Got the roundup part which is ROUNDUP(F2,-3) but having hard time with the other part If the cell (F2) is 0 or below would like to show zero. I am attaching a spreedsheet so you can see what I'm trying to get at.

View 5 Replies View Related

Rounding Up A Cell Value To Next Highest Whole Number

May 4, 2006

I have a whole number value in cell F18. In cell F24 I want to enter a formula that allows me to divide the value in cell F18 by 4 and if the result is not a whole number, round it up to the next whole number.

View 2 Replies View Related

Complex Formula

Mar 15, 2008

i have this formula that is complex ( atleast it is to me ) and i need to add more to it. Here is the formula:

=VLOOKUP(" "&LEFT($A5,FIND("[",$A5)-2),'7E'!$C$2:$D$682,2,FALSE)*0.99

What i am needing is, when it gets the price from another worksheet, if that price is below 0.04 that it makes it 0.05

View 9 Replies View Related

Dictionary And Complex Transposition

Jul 29, 2014

I wish to make a transposition using dictionary. I wanted to take a script jindon, but I have difficulties to adapt.

VB:
Option Explicit
Sub test()
Dim a, i As Long, ii As Long, w, rng As Range, cpt As Byte
a = Cells(1).CurrentRegion.Value

[Code] .....

View 9 Replies View Related

Complex Formula Edit

Apr 22, 2008

The formula below works apart from the first bit which i want to be if F23 says 'fail' then i want the cell to say 'no dissertation' but if f23 says pass then i want the formula past that bit to be put into action.

=IF(F23="Fail",0)*IF(COUNTIF(B22:B45,"Dissertation")=1,IF(VLOOKUP("Dissertation",B22:C45,2,0)>39,LOO KUP(F26,{0,40,50,60,70},{"No Dissertation","3rd","2:2","2:1","1st"}),"No Dissertation"),"No Dissertation")

isolation123

View 13 Replies View Related

Creating A Complex Formula With UDF?

Jul 21, 2014

I am creating a complex formula with UDF, this Hlookup is part of my complete formula. Meanwhile, I am working on this piece, not Sure what I am doing Wrong

I try to conver "MonthCurrent" Variable in a Year because the MonthCurrent is 06/01/2014, so will be Year(6/1/2014) = 2014

My Range called "DailyRates" is based per Year = 2014, 2015 ......

[Code]......

View 2 Replies View Related

Complex Average Formula.

Feb 3, 2009

The number of columns vary, but the number of rows is constant. Lets assume, max_col is the number of columns and max_row is the number of rows per report.
Then, I need the following logic as VB macro.

for row = 6
F6 = (G6*G4) + (H6*H4) + (I6*I4)+...+ ("max_col:6" * "max_col:4") / (G4+H4+I4... "max_col:4")

View 2 Replies View Related

More Complex Fit Function Than LOGEST

Jan 14, 2010

The function LOGEST fits to the equation y=b*m^x.
Does anyone know if more complex functions can be found in Excel?
Ideally I would like to fit to y=b*m^(x-a) + c. If anyone has come across a method, or some code, which allows this.

View 14 Replies View Related

Complex Numbers And Functions

Nov 27, 2007

I have started to develop my own userdefined functions for complex analysis. I have some problems, but I believe they will be solved soon.

The arrays are defined type complex with the
Type Complex
re As Double
im As Double

End Type
sub refleks
dim sa as complex
dim akd(150) as complex
.
.
..........................

View 9 Replies View Related

Required For Complex Lookup

Nov 12, 2008

I am trying to summarise training data in a spreadsheet as per the following layout:

A B C D E F 1 Trainer name # of trainees # of assessments Total hours Avg hrs per trainee Pass rate 2 Peter

3 Paul

4 Mary

The intention is to have this summary fixed on one tab, with the raw data being dumped into a second tab alongside the summary (named “the data”).

The data for the summary comes directly out of an Access database and is in the following format (I’ve put an X against the headings that aren’t relevant to the summary):

A B C D E F G H I J K L M N 1 X X PersID Name X X X X X Trainer Assessment X Result Hours 2

12345 Jimbo

Peter Excel
Competent 2 3

54321 Nelson

Paul Access
Not yet Competent 3
Notes:

-The data can be up to 2000 rows long

-There will be multiple entries for each trainer of course, and multiple entries for some trainees (i.e. they will complete several training types)

-Result can only be Competent or Not Yet Competent

So, of the 5 fields I need to populate for each trainer, I’ve currently only got the following nailed:

# of assessments: =COUNTIF('The data'!$A$2:$A$114,A2)
Avh hrs per trainee = D2/B2

These were the easy ones.

What I’m having trouble with is the following:

For the #of trainees field, the calculation must determine how many people were trained by each particular trainer – so only unique entries should be counted (PersID could be the unique identifier).
Total hours will also need to be linked to the individual trainer – in other words, how many hours in total has the trainer spent training?

Pass rate is another tricky one – for each trainer, I’d like to see the percentage of trainees deemed competent out of the total number of trainees they trained.

View 9 Replies View Related

Complex Conidtional Sum Question

Aug 10, 2009

I have searched for awhile, but I am stuck on an equation that I could use help on. A little background, I am developing a staffing profile, which I previously was using pivot tables and then using a "=getpivotdata " forumla to pull the data. I then found the conditional sum formula and like it's capabilities as I wouldn't have to worry about human error if I forgot to update a table or if the data in the table wasn't updating correctionly. I am doing a 2 entity conditional sum but now I need to incorporate a simple if statement as well. What I am trying to do is add another formula stating that if the formula comes back with zero data, i would like to make the answer blank For example ... =if(D3="","") . The reason I need this, is I have a tab for each month. As it is Aug now, I have actuals spend until July but don't have anything yet for the remainder of the year. When I am calculating my averages for the remainder of the year, I don't want the data to be scewed by all the zero's. My question is, is it possible? I tried to add it and did the CSE trick and got an error message. I thought you guys could help. I hope I was clear. Please let me know if I was not and I can expand. Below is the formula I would like to add another if statement.
=SUM(IF('Jan'!$D$3:$D$1500="TD",IF('Jan'!$H$3:$H$1500="Supply Support",'Jan'!$AB$3:$AB$1500,0),0))/H$2

View 9 Replies View Related

Complex Issue, Can It Be Using Formulas?

Sep 1, 2009

I have the following template: -

Rows C5:N5 = Months Jan - Dec.
B6 = Finance
B7 = Marketing
B8 = HR
B9 = IT
B10 = Operations
B11 = Legal
B12 = Service
B13 = Procurement

Rows C6:N13 = Randomly Generated monetary values (#.##). Note that some cells could be blank, indicating zero.

B20 = Finance
B21 = Marketing.........................

View 4 Replies View Related

Complex Vertical And Horizontal Lookup

Feb 2, 2014

I've got the following complex issue:

...................Trader1 Trader 2 Trader 3 Trader 4 Trader5... Trader20 (Horizontal)
Commodity1 .....0.....2.....0.....1.....1
Commodity2 .....0.....4.....0.....-3.....0
Commodity3 .....1.....0.....0.....5.....0
Commodity4 .....0.....0.....2.....0.....0
.
.
.
Commodity 36 .....2.....1.....1.....0.....0

I have 5 cells that are basically a drop down list of all the traders, so the user can select up to 5 traders.

Once the user selects up to 5 traders (lets say Trader 1, 3 and 5 for our example) I have the following requirements to fulfill:

Displaying all commodities that have non-zero, non-negative values associated with those traders in a list somewhere.

For our example, this would then display:

Commodity 3
Commodity 4
Commodity 1

View 4 Replies View Related

Convert A Complex Excel Spreadsheet To Web App?

Sep 16, 2013

to convert a complex Excel file to a Web App (usable in the browser of a iPad) which has 15 sheets and some input.I tried every javascript/jquery library, tried to hire a freelance, asked for a quote everywhere, tried add-ins, etc. Nothing seems to work. Manually, well, it is pretty impossible within 2 days.

View 3 Replies View Related

Time Error '16' Expression Too Complex Run

Dec 7, 2009

Using Excel 2007 the below code causes Run Time Error '16', Expression too complex.

It works fine in Excel 97, 2000 and 2003.
why this would now cause an error?
My search on google only refers to this error in relation to charts.

View 6 Replies View Related

Complex Logical/conditional Formula

Jun 12, 2006

I am working between two worksheets. In one worksheet I have a list of
account numbers. In the second worksheet I have the same account numbers in
a different order. What I am trying to do is, If the account number from
worksheet one is found in the column with the account numbers in worksheet
two, I want to display the matching title for the account number that is in
the same row but different column in worksheet two.

So essentially (if acct # from column x in wks 1= an acct in number in
column m from wks 2, display the corresponding title in the same row of the
matching acct # found in column m). Is this possible?

View 10 Replies View Related

Complex VBA / Formula To Multiply Values

Aug 9, 2012

I have a complex request which I think can be done either via vb or a macro, not sure which is best?, will try to explain:

The table shows a list of items that make up various BOM. there are quantities of each item required at the different BOM level

0 = Top level
1 = Level Below
2 = Level below that etc, etc

If the top level (0) has a quantity greater than 1 then all the items at the lower levels should be multiplied by that quantity

THEN move to the level 1's and whatever the quntity is in the level 1 field, multiple all lower levels by that number
if the quantity fields are blank, then always multipy below by 1

This needs to continue on down the chain of levels

So basically you end up with the correct quantites of each item at each BOM level.

View 9 Replies View Related

Goal Seek With Complex Numbers

May 24, 2008

I'm trying to understand how complex numbers are handled/processed in Excel.
As related to my application, an interesting exercise would be to use Goal Seek w/s command to find the roots of the equation:
X^2 + 4 = 0
setting the (rounded) value in cell A2 to 0 by changing A1

A1:: 1+i
A2:: =COMPLEX(ROUND(IMREAL(IMSUM(IMPOWER(A1,2),4)),6),
ROUND(IMAGINARY(IMSUM(IMPOWER(A1,2),4)),6))
Obviously a conventional or direct use of Goal Seek wouldn't work since Excel treats complex numbers as text.

Perhaps, one should use Goal Seek twice in this case:
first: find the coefficient "a" for IMREAL(A2) = 0
second: find the coefficient "b" for IMAGINARY(A2) = 0
and the root would be "a+bi".

View 9 Replies View Related

Attempting Complex Lookup And Match

Sep 7, 2008

I am attempting to match data entered into two cells. The data input goes into Worksheet 1. In Column A Row 1 the user inputs data... Example: 5. Then in Column B Row 1 the user inputs separate data... Example: B. I would like the result returned to Column C Row 1. The lookup would be on a table in Worksheet 2.
Worksheet 2

A B C D E F G H I J K L M N O P
1 2 3 4 5 6 7 8
A Pig A Cat A Dog A Fish A Ox A Bat A Six A Pie
B Pie B Six B Bat B Ox B Fish B Dog B Cat B Pig
C Fish C Ox C Cat C Six C Dog C Pie C Pig C Bat

Therefore the Entry 5, then the entry B would Return: Fish
So it's a lookup and match Column A-P. Then using that match lookup Row 2-4 for the second match. Then Return the data from the cell next to it.

View 9 Replies View Related

Complex Text Formula Formatting

Sep 8, 2008

I am trying to generate a formula for formatting text that will remove all spaces, comma's etc. Examples as follows:

The Bank Job -> the _bank_job_small.gif
Picture This! -> picture_this_small.gif
War Games: The Dead Code -> war_games_the_dead_code_small.gif
Chacun son combat (Never Back Down) -> chacun_son_combat_small.gif

I've figured out a formula that works but it seems overly complex - it uses multiple SUBSTITUTE, SEARCH and REPLACE functions. Is there an easy way to remove a range of symbols i.e. ! : ( ' ?

View 9 Replies View Related

Complex Multiple Cells Comparison Formula

Jan 16, 2014

I've tried some of the basic formulas, but this is beyond me.

I have table A of 500 rows and need to compare it to table B of 2000 rows.

A
B
C
D
E

1
D6 - 1.01
Table with PC
3
Yes

2
D6 - 1.01
Bookshelf
4
No

[Code] .......

For example with these two, from the second table to the first. IF column B equal to column and the corresponding column C equal to the corresponding column C, return for example "yes". Then I need to check the same thing for the corresponding other columns, but basically everything would be tied to B and C. The problem is the second table, larger one, is basically all over the place, I could manage to do this if we would be talking equal length tables, but I do not know what formulas I could use to check further down a row once we find an equal value.

View 5 Replies View Related

Dificulty Finding The Right Combination For A Complex Lookup.

May 19, 2009

I am having a difficult time with a look up. It would be very hard to explain so I'll attach a copy of the section of the worksheet that the problem lies on with comments so you can see whats going on. The problem there is a numbered list with a reference number i can't seem to figure out a lookup that will look in the chart above and find the row associated to the reference number and according to how many before it have that reference number find a secondary reference number listed in the column above. The attachment should clear it up.

View 4 Replies View Related

Return Row Number Of Complex Matrix Values?

Oct 30, 2013

In the attached sample file, one can see the setup of my data. I'm trying to reference the row number of a certain cell in a matrix with many cells of the same value. I have different Headers of each row, which should make it possible to use INDEX, MATCH or VLOOKUP. but i don't seem to be able to get it right.

Description.I need to return the "class" och each of the capability elements, i.e. If the capability in "Soups" is marked ("X"), at class 3, i need the integer "3" to be shown here.

View 12 Replies View Related

Creating A Complex Scoring System On Excel?

Jan 8, 2014

create a unique scoring system on a set of given criteria that enables an even split in work for team members. so i.e. work comes in, and depending on the criteria of work its then passed to the relevant team member based on points. At the moment work is allocated A-Z which is working out unfair on team members as some are getting more/less work than others...a points system will enable a even split. so if a piece of work scores 30 points it goes to a junior member, if a piece of work scores 80 points it goes to a senior member and so on...my problem is how to link the points and criteria...Im guessing joint vlookups will be needed.

the only other way i can think a system is similar is like a fantasy football system...points based on criteria.

View 3 Replies View Related

Complex Logic Formula To Delete Values

Nov 14, 2009

upon further consideration I thought that it would be best to probably do verything in two passes or maybe not see below macro to get an idea of how the end process should function

View 4 Replies View Related

Complex Character Combination Validation Code

Dec 15, 2009

I really need a validation code for Cell B15. I realize that a macro could do this, but a validation code is what I really would need:

Cell B15 can only allow at least one of the following values, or two or more of the following values separated by '&' (Note the spaces between the digits):

I
I I
I I I
IV
IA
I IA
I I IA
IVA

or (some combination examples):
IA & I I I
I I & I I IA
I VA & I IA

If the user fails to meet these requirements, then he should get an error message telling him to try again.

View 14 Replies View Related

Complex Array Formula - Scores And Weightings

Oct 10, 2008

I'm basically creating a scoring worksheet, where there are a list of questions. Each question has a weighting as some are more impotant than others. Each question also has a range of answeres as some may not be clear yes/no answers. Also not all questions apply to all assessments so some may be left blank.

I basically need a formula which does the following to produce a total score:

[Total awarded score] / [Total available score of questions scored]

which equals

[(score <> "" / range)*weighting] / [if score <> "" sum weightings]

I've attached an example document where I think I'm getting close, just not sure if its possible.

View 4 Replies View Related







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