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
ADVERTISEMENT
Feb 12, 2009
How do you do two "if" statements?
I need to have two tests before i get a final result so i need to know the protocal to get two if statements.
View 14 Replies
View Related
Apr 20, 2006
I have a sheet going like this:
CODIGOALBERTMARIODOUGLASEDYCARLOSJOHN
2818182768280
3858787818684
7828186768481
9877889928789
12828282788380
...
I need to colored the the max and lower value for every row (green for low and red for high) i did it well for first row with conditional formatting but i cant copy for the anothers ones and i dont want to do it row by row (i am so lazy). I did this for first Value and the copy format for the following values in row:
Formula Is =IF(ISBLANK(B5),FALSE,MIN($B$5:$G$5)=B5) (green color)
Formula Is =IF(ISBLANK(B5),FALSE,MAX($B$5:$G$5)=B5) (red color)
The reference must be Absolute cause another way results become wrong for another values, but thats the problem cause i cant use it in below rows.
View 2 Replies
View Related
Apr 28, 2006
I run a report that has several blocks of information with blank rows separating them. The blocks change from time to time and across pages so they are not always in the exact same rows.
I'd like a macro that can insert page breaks around a certain number of rows (say 60), but not insert one between two rows that have any content. If the page break would fall between two filled rows I'd want it to go to the nearest blank row instead.
The pages would be about the same size but the blocks of information would not be broken up. That's my idea, anayway. I don't know VB so I can't be sure it's even possible.
Provided this is possible, I'd greatly appreciate the help, as would all the other people in my organization who have to do this on a daily basis.
View 7 Replies
View Related
Apr 8, 2009
I am trying to create a logical test with three possible results:
h2 <10.2 "needs improvement"
(if false) h2=10.2 "meets standard"
if false h2>10.2 "exceeds standard"
Basically, I am referring to a cell to determine if it meets, exceeds or does not meed a specific standard. In this case 10.2.
View 4 Replies
View Related
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
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
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
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
Jun 22, 2014
In column N I have a list of dates. In column Z I want to display either "Yes" where the date in column N has already passed or "No" if the date in column N has not passed. The formula I'm using in column Z is =IF(N2<TODAY(),"Y","N") however it just gives me "No" on every row regardless of whether date is passed or not.
View 11 Replies
View Related
Dec 16, 2008
I need a solution for this situtaion! Iam using a french version of excel2003!
I have #DIV/0! in P24 (i.e an error value) So I need to check in Q24 such that if (P24=#DIV/0!,0,P24). I need to have a logical formula for this one!
View 3 Replies
View Related
Sep 14, 2009
How to find whether a particular cell is blank or is text in a given row/column?
To elaborate more, I have some data in a row:
A B C D E
15 N 16 17 18
I am using a below formula to find whether there is any text in the row
ISTEXT(row(A1:E1)) but I am getting result as False.
View 9 Replies
View Related
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
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
Feb 4, 2014
2 formulas that I need for my work (related with linear recurrences; this is serious work). First, I would like to know if is it possible to write a formula (or a set of formulas) that allows to write in a first column, in the first cell, the number 1 and in the following m cells, m distinct numbers chosen in a set S of n numbers (n > m); in the second column again the number one, and another set of m distinct numbers chosen in the same set S (here the order of the elements matters which means that the set {1,2} can be written as (1,2) and (2,1)). And I would like to do this until all the choices of m (ordered) numbers chosen in the set S are exhausted. Example: S = {2,3,4} which means that n=3. Suppose m=2. Then the columns would be
111111
223344
342423
The second formula I'm searching is (I think) a logical formula. What I would like is a formula that checks if the numbers in a fixed number of consecutive cells of a column are distinct and also non-null. If that's the case the formula should assign TRUE and if (at least) two cells are equal or (at least) one is zero, it would assign FALSE.
View 2 Replies
View Related
Sep 16, 2009
I want to use the If formula but want the value if true/false to be another formula, not just a set value. Here's what I came up with (obviously wrong, please excuse my lack of experience) =IF(D2>C2,"=(D2-C2)*5","=((60-C2)+D2)*5 ")
View 2 Replies
View Related
Sep 28, 2009
I am trying to get more than one outcome from a logical function in a single cell. I am teaching a class and i want this particular cell to add up all the points from tests and quizzes and then give me an outcome of a grade (A,B,C,D, or F) Right now i can only get the cell to give me an outcome of A or B. I need the cell to be able to give me 5 possible outcomes (A,B,C,D,F).
Right now i have this formula in this particular cell......=IF(Q6>234,"A")......so if Q6 is greater than 234pts they get an A...if not they get a B. How can i also give ranges of total points to have the cell give C,D, or F?
View 5 Replies
View Related
Jul 10, 2013
I have following formula that returns #value!
=IF(OR(OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D))="",OFFSET(LIVE_JNL,ROW(5:5),
COLUMN(C:D))=0,""),OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D)))
Where the value in the cell which is referenced is either blank or zero then blank must be returned, otherwise the value which is being referenced by the offset formula.
View 2 Replies
View Related
Aug 8, 2007
what was wrong with this formula, an error message comes up saying VALUE, im trying to get the formula to say that - If 2 dates are different then subtract one from the other to get a number of working days, but if the dates are both the same then to -1 from the value as for some reason the value is coming back as 2 when it should be 0?
=IF(OR(M17>0,N17>0),NETWORKDAYS(M17,N17,holidays)+1+Q17,IF(OR(M17=N17),NETWORKDAYS(M17,N17,holidays)-1,0))
View 9 Replies
View Related
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
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
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
Feb 19, 2014
I'm trying to create a formula that will determine the bonus (%) for sales made. Here's the table:
<10000 sales = 0% bonus
>=10000 sales but <=29999 sales = 5% bonus
>=30000 sales but <=to 44999 = 7% bonus
>50000 sales = 10% bonus
Since the sales and bonus figures are subject to change each year I'd like the formula to point to the numbers but that part seems easy if I can get the formula right.
View 3 Replies
View Related
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
Dec 2, 2009
Sometime it’s so hard to explain what it is you are having problems with, specially in the little title. I'm trying to write a formula which should be very simple but isn’t . The formula is in box F10 if B10 has anything in it and its going to have a mixture of text and numbers (first text then numbers) then it’s should equal to the value in the box C7. Here is the formula I wrote that doesn’t work.
View 4 Replies
View Related
Apr 15, 2014
Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:
(MEDIAN(IF(DATA!$B$15:$DZ$15=1000,DATA!$B26:$DZ26))) and works perfect.
However I can't figure out how to manipulate it into a percentile keeping the condition of
IF(DATA!$B$15:$DZ$15=1000)
View 2 Replies
View Related
Feb 20, 2009
I am trying to use an OR in a VBA IF statement. The condtition finds one vale but not the other. Due to the nature of the data, a value is marked either on, off, or both.
I have two seaches in the macro: one looks for on and both, and runs a sub, the other looks for off and both and runs a different sub. However I can't get the both case to run as it should.
If (Cells(i, CurCol).Value = "On") Or (Cells(i, CurCol).Value = "Both") Then
Cells(i, CurCol).Select
View 9 Replies
View Related
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
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
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
