# Using IF Function To Use Different Formulas Depending On Different Rules?

Nov 27, 2013

I have got to this stage with a formula but cannot understand why it isn't working:

=IF(OR(A2=1,A2=2),(MIN(B2:B6)+50),(MAX(B2:B6)+50))

If A2 equals 1, I want it to add 50 on to the smallest (earliest) date in C2:C5.

BUT

If A2 equals 2, I want it to add 50 on to the largest (latest) date

The formula just adds 50 on the smallest (earliest) date, whether A2 equals 1 or 2.

What am I doing wrong?

## Different CONCATENATE Rules Depending On Cell Value

Oct 28, 2009

I'm trying to do a CONCATENATE function in Excel 2003. The first part of the function (this is working correctly) is a) concatenating 4 cells from a different worksheet (WB_NEW) into one cell and is b) putting a space in between each entry:

=CONCATENATE(WB_NEW!AH3," ",WB_NEW!AI3," ",WB_NEW!AJ3," ",WB_NEW!E3)

What I'm now trying to do is enhance this function to perform with 2 separate concatenation rules i.e. "if one of the cells specified above is blank, concatenate a different set of cells".

## 3 Color Conditional Formatting Formulas Rules?

Jun 23, 2012

How do I create 3 color conditional formatting formulas for the following rules?:

if today is 15 days or greater before the day in the cell then green

if today is between 14 and 7 days before the day in the cell then yellow

if today is within 6 days to zero days before the day in the cell then results

anything is clear

x8:x21
5/10/2012
5/14/2012
5/21/2012
6/11/2012
6/12/2012
6/12/2012
6/12/2012
6/20/2012
6/22/2012
6/23/2012
7/4/2012
7/5/2012
7/8/2012
7/12/2012

## Applying Certain Formulas Depending On Dropdown Selection

Dec 6, 2013

Any way to apply a formula to a cell depending on what choice is made from a drop down box in a cell that it refers to.

Hypothetically:

I have a drop down box in A1 with 2 options: Cookie Making Costs - or - Cake Making Costs
In cells A3 through A10, I have cells with the costs for all the ingredients (and B3 through B10 for cake ingredients).
If I select Cookie Making Costs, I would like B1 to apply a formula that sums up all the costs associated with making cookies "=sum(A3:A10)"
If I select Cake Making Costs, I would like B1 to apply the sum of the cake ingredients "=sum(B3:B10)"

## Returning One Of 2 Formulas Depending On Contents Of A Cell?

Jan 27, 2014

I have a cell in which I am entering either the letter "C" or "S".

I then want the following to happen......

If the cell contains the letter "S" do = IFERROR(VLOOKUP(\$B7,'Asset Growth Rate'!\$A\$2:\$F\$39,2,FALSE)*\$C7,"-")

If the cell contains the letter "C" do = IFERROR(VLOOKUP(\$B28,'Asset Growth Rate'!\$A\$2:\$F\$39,3,FALSE)*(\$C28+\$F28),"-")

The formulas themselves are already working in separate cells, I now just want to combine them depending on whether the cell contains S or C.....

## Conditional Format (with Formulas) 10 Cells Depending On 1 Criteria

Oct 19, 2009

I want to colour 10 cells (A1-J1) if I type in yes in cell K1. Using conditional formatting Im only able to colour 1 cell. Is this possible without using VBA?

## Function To Sum Cells Depending On Month Chosen

Jun 3, 2014

Is there anyway that when I choose (for example, Jan) the result shows the sum of January numbers. On the other hand, if I choose Feb, the results would be the sum of February. See Mar on cell C8 and Feb on cell E15.

A
B
C
D
E

1

1st
2nd
3rd

2
Jan
1
2
3

[Code] .......

## Function That Distributes Numbers Depending On The Quarter And Year?

Jun 10, 2014

I'm trying to find out a function that allows me to evenly spread the amounts given a certain start year and quarter and end year and quarter. Let's say, I have 20 dollars and the starts in 3Q/2012 and ends in 2Q/2013. Then, the money should be spread out in four quarters. If the end is in 3Q/2013, then the spread is five quarters, and the amount distribution automatically changes.

Attached is my spreadsheet.

## IF Function To Display A Message Depending On Percentage In Cell

Feb 21, 2007

Im currently studying for an ICT A level and im in my first year doing AS. Im working the AQA board and i have a project that i have a target for an A! I need to create a system for a business and write a report on everything i did...wont go into detail. Anyway. i am having trouble with an IF FUNCTION!!! I have a cell (E35) that contains a percentage.

I want the IF function to display a message depending on what the percentage in cell E35 shows;

IF, 0>> Display "Bad"

40>> Display "OK"

70>> "Good"

90>> Display "Very Good"

E35>=100 >>>> Display "Excellent"

I have tried so many things and I was so sure i entered it correctly, but it keeps coming up with an error.

## Changing Range Of Sumproduct Function Depending On Length Of Data Series

Feb 20, 2007

I have a small problem using the �sumproduct function� which I am using it to calculate the area under curves using the following equation (=SUMPRODUCT((B10:B109-B9:B108)*(D10:D109+D9:D108))*0.5). However I have hundreds of curves to calculate the area under and the length of the data series for each curve is different, I am currently changing the length of the data series by hand. I have attached an example worksheet of 2 sets of data (the formula I am using at the moment is highlighted in blue � row 203). Is there are function or formula that I can use inside this equation to change the range of the formula depending on the length of the data series?

## Want To Make 1 Function From 5 Formulas

Aug 29, 2008

******** ******************** ************************************************************************>Microsoft Excel - Book3___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD1ABC�2464104641046410�32081500002081520815�44062000040624062�51521259171521215212�Sheet1�[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

What I am doing is comparing Zip Codes from 3 Data Bases against each. If all 3 DB's are equal then I need a responce of "Ok", if there is a difference in any of the 3 then I need a "Bad". I know this is simple, but Data Base A can have the Zip Code as all 9 numerals or just the first 4. It only depends on who entered the Zip whether or not the trailing 4 numerals were entered or the were entered as all zeros. To throw another wrench in it, if the leading numeral is a zero, all three DB's will drop the leading zero.

I want to do all of this in 1 Function, not 5 fromulas.

What I need is for Database A, test the cell for Length and if it is 8 or 9 numerals then remove the right 4 to only leave the left 4 or 5 numerals (4 if it had the leading dropped 0) and then do a simple If A=B and A=C Then "Ok" Else "Bad".

## PRODUCT Function With Nested IF Formulas

Dec 8, 2009

I receive montly investment performance projections going out monthly for 10 years. I need to show the return annually for each year. I created a SUMIFS formula to sum the percentages between two date ranges (beginning of the year and the end of the year for each year). However, my SUMIFS formula does not take into considersation compounding. How can I modify the SUMIFS formula I created to take into consideration compounding?

I know how to convert monthly percentages to an annual percentage,using the PRODUCT formula =PRODUCT(1+T15,1+U15,1+V15,1+W15,1+X15,1+Y15,1+Z15,1+AA15,1+AB15,1+AC15,1+AD15)-1, whereas T15:AD15 is the monthly percentage gain. Is there a simplier/alternative way that I can incorporate into the SUMIFS formula?

If it is too complicated to utilize a SUMIFS formula, I would like to be able to select a range of cells and have Excel calculate the result vs typing "1+[select the cell]," for each month. It takes me forever and open to input errors. It would be much easier to be able to select a range of cells when creating the formula.

## Function/Formulas Not Automatically Updating

Jun 25, 2008

I'm using a heavy excel file and I have linked some cells in one sheet to other worksheets in the same file. My problem is that when I change the value in some cells they do not change in the other cells linked to the ones I have modified.

## Save Formulas With Weeknum Function ( With Workaround)

Apr 1, 2009

I have some formulas in my workbook that use the weeknum function from the analysis toolpak. I have the analysis toolpak installed, yet every time I save the file and reopen it, the formulas with weeknum are gone, with #N/A in its place as the formula. Sometimes the results of the formula from the last time remain (the cells contain the number of the week, but without the underlying formula), sometimes not. All the other formulas are fine. The formulas are simple, like =WEEKNUM(C2)

I have been trying to save as a 97-2003 .xls file. Today I'm trying to save as .xlsx, but I have to rebuild the formulas first, so I don't know how that's going to turn out.

## Insert Formula Depending On Cells Depending Of Variables?

Jan 27, 2014

In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:

[Code] .....

All my variables are declared:

Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed

The error message I receive is the following: "Run-time error 13, Type mismatch"

I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.

## ABS Function And Named Array Formulas To Find Closest Value?

Feb 5, 2012

I am really struggling with the following formula:

MATCH(MIN(ABS(Ann_TaylorRegularBust-B4)),ABS(Ann_TaylorRegularBust-B4),0)

I am trying to find the cell in the named row "Ann_TaylorRegularBust" that is closest to the input bust size in cell B4. This formula works when I use actual range instead of the named array.

## Help With IF Rules

Jan 8, 2009

I am creating a worksheet that I need to encorporate an If statement into. What I need to happen is when cell A1 has a number in it (1,2,3,4...) I need it to perform a function on cells B1 and C1. However there will always be information in cells B1 and C1, but I only need it to perform the function when the number in cell A1 is anything but 0.

## Creating Rules For A Table?

Dec 9, 2013

How can I create a rule for a table that finds a specified word and replaces it with another such that the rule would continue to apply to the table even when a new record (or row) is added. There has to be a simple way to do this without a macro.

## VBA Not Following Consistent Rounding Rules?

Jun 13, 2014

This Subroutine:

[Code] .........

Seems to behave differently each time it is called from my main Loop. As an example the first loop finds:
TotalTubes = 6
TubeRows = 4
TopRowTubeCount = 2
Where 6/4 = 1.5 and is rounded up to 2

The controlling variable in my code for Length is TopRowTubeCount and it is a Public Variable Dimensioned as an Integer

However on the next loop it finds:
TotalTubes = 210
TubeRows = 4
TopRowTubeCount = 52
Where 210/4 = 52.5 and is rounded down to 52?

I even used the round function for the TopRowTubeCount calculation and the erratic behavior continues.

## InputBox Validation Rules

Jul 12, 2006

I have 2 input boxes. Input Box 1 asks for user's Date of Birth. Input Box 2 asks for user's name: Pretty simple right! They will loop until following 2 conditions are met: If the user enters something in them OR user presses Cancel. If they are empty and Ok is pressed then keep on looping.

## Some Cells Not Following Protection Rules

Aug 5, 2006

I have set all cells in a worksheet to be protected. I then selected specific cells that are not protected. The problem is that cells A11:D29 except C13:C14 do not follow the protection rules in that I can edit them (but when I right click, I cannot format, just like they're protected). These cells are NOT part of the group that was selected to be unprotected. When I unprotect the sheet and check the protection setting for these oddball cells, it shows as protected. All other protected cells do not allow entry (which is what I want).

## Display Conditional Formatting Rules?

Oct 9, 2013

how to display the current set of conditional formatting? I don't want to go to the cells I want a listing of all the conditional formatting rules. I am sure I have seen this somewhere in the past but cannot locate it.

## Combine 2 Conditional Formatting Rules?

Apr 12, 2014

I have two conditional formatting rules that work independently, but I am looking to combine them into 1 rule, making (2) subject to (1) when (1) is true

(1) =\$B3:\$B6="y"
(2) =AND(COUNTIF(K\$3:K\$6,MIN(K\$3:K\$6))=1,K3=MIN(K\$3:K\$6))

## Setting Rules For Multiple Of Number?

May 29, 2014

I have started to use excel again because I need to create a spreadsheet for maintenance hours done on a vessel. The problem I have right now is, how to set a rule to allow only certain multiple of a particular number to appear. For example,

I have a initial cell value where I have to manually insert the total number of hours that have gone by in the vessel. Let us say that this value is 500 hours.

In another cell which is labeled, "Last Completed", it would have to read at how many hours this job was last completed.

I also have another cell which gives you the time interval before the job is performed again. This value is 250.

So if the current number of hours is 500, the last completed date should be 250. (assuming Maintenance was done at 0 hours and 250 hours).

How do I make it so that the "Last Completed" cell would read 250 until I hit 750 hours which at that point should read 500 Hours.

## Calculating A Discount Based On Certain Rules

Feb 11, 2009

I'm looking for assistance with working out the following formula. I live on an apartment complex in Marbella Spain and we pay Monthly Community Fees of say \$100.

We want to calculate a penalty amount for late payers of say 30% of the monthly fee due for non payers? So for instance if the monthly amount due in Cell D8 = 100 and the actual amount received in cell E8 shows 0 or then increase any figure due for the following month in Cell F8 should increase by 30% (\$ 130) in the following month. and so on

I use Excel ver 2003. Trust my query is reasonably clear?

## Automatic Fill Cells According Some Rules

May 15, 2013

I would like to fill some cells with a customer id if this customer is the same as the cell bellow. As my example below.

CUSTOMER idCUSTOMER NAME TYPE
737346 Nikos 81
1154765 Kyriakos 81

1154780 Thanos 81

I would like to fill automatically the empty cell bellow Kyriakos the customer id 1154765 in new column. I want in column "new" to fill with customer id and when the customer id is empty to fill the same customer id as above customer id.

Like the example below

CUSTOMER idCUSTOMER NAME TYPEnew
737346 Nikos 81 737346
1154765 Kyriakos 81 1154765
1154765
1154780 Thanos 81 1154780

## Copy Date Rules With VBA Code

Feb 18, 2014

I have a file which I want to copy the lines.

I have made a test file for clarity. See annex.

## Delete Rows Based On Rules

Aug 21, 2007

I am trying to clear row with the following rules:

the last cell must not contain 0, if 0 delete entire row.
the first cell the the A row should be only three character long, if more delete entire row.

Now I managed to delete the 0 in the last cell and but cannot get the VBA to count the characters in the first row and if more than three, delete the entire row . I know to count the characters in a cell is done by Len(Rng.Value) , but getting the VBA right is alluding me .

the script is here,

Sub Del_rows_with_zero_in_column_of_activecell()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Const StartRow As Long = 1 'Row to Start looking at
Dim StopRow As Long
Dim Col As Long

## Custom Rules For Sheet Naming

Apr 4, 2008

I want something strange but i guess you can do just about anything with excel these days if you ask around.

I want names for the sheets that are contained on a specific excel file that i have to be automatically generated accordingly to the following:

sheetname = runxyy_s=zz%

Where x is the lowercase letter of the word formed on the name of the first sheet of the workbook
and yy and zz are values stored on specific cells on the sheet,lets say yy is A1 and zz is H4.

## Permitted Permutation/Combinations With Some Rules

May 25, 2007

I have found this very challenging, and have really confused my self on how to systematically approach this. I have the user enter the value in the black highlight boxes, some subtraction produces a base value, the user may also enter the tolerance of the working value (base value), effectivlly giving rise to a small range to generate the permutations from.

What I would like to do then is find all permutations of the inventory (letters in the sheet) with their corresponding values right above them to the small range determined via the user (working value and tolerance). There are also quantity limits for each item. There four basic rules that need to be adhered to:

1) Permutation can't have both S and PP, so one or the other
2) Sum of H and HN must be greater or equal to sum of S and PP
3) H or HN or both must be present, so their sum cannot be zero
4) If HN is less than or equal to 2, and H is greater than 2, then NA and NG must equal 0.