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


ADVERTISEMENT

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

Logic For Sorting Values?

Feb 17, 2014

I have a dump which looks like below sample:

3
3
2
2

The actual dump will have around 200 - 500 rows of data.From the above I need to manually group them which are similar. E.g. the data of (3, 3, 2 , 2) in row 1 & 2 are matching, so they will be group 1 & row 3 will be group 2, row 4 will be group 3 respectively.

The sum & sort did not work as sometimes the data with the same range are not in same order. simpler logic/VBA code if any, which will automatically put group numbers if the data in two rows are exact match.

E.g of the output below for the above data:

Group No.

1
3
3
2
2

View 6 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

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

Complex Printing Of Values Of Unique Strings Between 2 Arrays?

Jul 11, 2014

I have the following arrays.

A1=Array("A","D","F")
A2 = Array(Array(0, 1, 4, 5), Array(2, 0, 1, 1), Array(2, 3, 11, 3))

B1=Array("D","X","F","J")
B2 = Array(Array(6, 0, 2, 1), Array(1, 1, 6, 1), Array(7, 9, 1, 0),Array(0, 3, 2, 2))

I would like to print in column A all the unique/common values between Array A1 and Array B1. The common values are A,D,F,X,J. And I'd like to print in Columns D:G the A2(i) values and print in columns I:L the values B2(i) in the corresponding row of values of arrays A1 and B1. If some string only appears in array A1 (i.e. "A"), this means that string "A" have values associated in array A2, but not in B2, then print zeros from I:L and if the string only appears in array B1 (i.e. "X" and "J"), this means that strings "X" and "J" have values associated in array B2, but don't have valus in A2, then print zeros from D:G for the same row of X and J.

View 7 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

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 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

Filter A Table With An OR Logic Based On Columns Values

Feb 10, 2010

i have a table and have some columns.

i want to filter the table so that i just see the records which have "text1" in ther column "F" OR they have "text2" in ther column "H", for example.

How can i implement an OR filter ?

View 2 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

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

Logic Formula To Calculate Distance

Jul 7, 2009

I have a excel sheet with city names in Column A, Distances in Column B, and Distances in Column C.

I wanted to write a formula so excel checks the distance between column C and column B and if there is less than a 10% difference between the two then it would copy the name of that city into another column D.

View 4 Replies View Related

Excel 2007 :: Complex SumIf / Sumproduct Formula?

Oct 11, 2011

I am trying to do a dynamic sumifs that allows the user to use data validation lists to select 5 items to query. This is the easy part.

The hard part is that the amounts range to sum goes across 6 weeks and I want the answer to also take account of another validation input which specifies up to what week the range should be.

I have done a simply if statement in row 8 that flags weeks relevant to input cell B6.

How do I do a formula in say cell A16 that does the sumifs and acknowledges that the user has requested a dynamic range to sum?

Sheet1ABCDEFGHIJK1RegimePost 20052TypeBudget<<<<- Dropdowns3Prod AreaLoans4ChannelRetail5Pro/ReReactive6Week18-May-1178Flags ->1110009Regime 2TypeProd AreaChannelPro/Re04-May-1111-May-1118-May-1125-May-1101-Jun-1108-Jun-1110Post 2005Budget Loans Retail Reactive 36942041320736540011Post 2005Budget Cards Retail Reactive 38423444021726636212Pre 2005Actual Mortgages Tele Proactive 35739840536923740113Pre 2005Actual Loans Direct Proactive 3213872554552254621415Answer16Excel 2007

View 3 Replies View Related

How To Create Formula For Stop-loss Logic

May 21, 2014

I'm trying to create an excel spreadsheet that keeps track of the highest value from a dynamic series. First a bit of background:

A manufacturer produces widgets made from gold. Therefore in order to quote manufacturing prices, they typically get a live gold price using Excel's web services and if the live price is higher that the last gold price they used, then they update their base price list for gold components. If the live price is lower than the last gold price they used, the last gold price does not change.

I'm trying to produce the above logic in Excel using:

Cell A1: [Excel data connection to pull live Gold price] - no problem here

Cell A2: dynamic 'price' that only updates if cell A1 is higher than cell A2

Cell A2 is where I'm getting stuck. I can't think of how to create a formula that updates cell A2 if cell A1 is higher.

View 3 Replies View Related

Macro Logic: Extract Higher/lower Values Based On Unique Cell

Jun 12, 2009

This is a logic based macro that needs to compare values in 1 column above a specific cell and take action until the column has no more data. Here's what I'm trying to do (and I've also attached a sample of desired results):

1) I want user to input starting cell. So I need to create a button for that.

2) This is where the logic begins and I'm having difficulty.
From cell defined in button, I move up one cell in that column and compare that value to the original cell.

If the value is greater than original cell then logic needs 2 outcomes:

a) move up 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to the right or 1c.
b) move up 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value one cell to the right or 1c.

View 2 Replies View Related

Static Formula Reference: Delete And Drag-and-drop Data To Get Rid Of Any Bogus Values

Jan 10, 2008

I making a template for multiple users to import data into for analysis. I want them to be able to delete and drag-and-drop data to get rid of any bogus values. However i need forumlas that reference those to NOT follow the values as there as moved. As a simple example say you have two columns, column A is a set of random numbers, column B is the number from coumn A times 2. If take the bottom half of column A and drag it up one cell, overwriting a cell, now the cell in column B next to the overwritten cell has a #REF err and ever cell below that now references a Cell in column A from the previous row. I want a formula that references a static address and will not follow the cell if it is moved.

If I use named ranges rather than specific cells references it works for the above example, however for my actual form it does not. Even though the ranges and formulas stay the same I start getting incorrect values. If there is no other way to this I will try to figure why the named ranges do not work.

View 3 Replies View Related

IF Formula: Conditions Within The "logic Test"

Nov 13, 2008

I'm trying to use the following IF formula:

=IF(J2>$J$143 and J2<$J$144,"1","0")

But it doesn't work - I obviously have the formula wrong. The problem arises with the inequality. how to have two conditions within the "logic test".

View 4 Replies View Related

IF Function Formula Logic: For Every Increase Of Of Mhz, Give 25.00 Per 100 Increase

Nov 2, 2006

I have a test due in the morning, and I really need this question answered ASAP, if anyone could. I need to create an IF formula for this situation: the standard Mhz is 500. Give 55.00 for that standard. But for every increase of of Mhz, give 25.00 per 100 increase.

View 2 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 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 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







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