# How To Write IF AND OR Combination Formula

Dec 24, 2012

My Columns are as follows:

A1-Criminal Name, B1-Crime, C1-Age, D1-Ratings, E1-Punishment

In the punishment column one will get 22 years life imprisonment if he fulfills the following conditions.

1. He must have done Rape OR Murder

AND

2. His Age should be >30

AND

3. His Ratings should be>8

It should throw 22 years in the Punishment column only if the above conditions are met otherwise it should be Nil.

1. Crime column includes Murder, Rape, Robbery, Assault, Kidnap etc

2. Age column ranging from 22-75 years.

3. Ratings column ranging from 1-10 points

4. There are 3400 records we have in the list

How to write an IF AND OR combination formula for this ?

## Nested Formula - If And AND Combination

Jul 23, 2013

I have to write a formula which states the following:

if cells AA1,AB1 &AC1 = 0 then "Slow-Moving",
if of these cells AA1,AB1 &AC1 contains a number then "OK",
if cells, AA1,AB1,AC1,Z1,X1,Y1 all = 0 then "Non-Moving"

I believe an If and AND combination could work but its not working for me.

## Formula Value Based On Combination Of Two Other Cells?

Mar 10, 2014

Modifying my formula to work. I'm trying to have the value of Cell B2 based off the combination value of two other cells.

[Code].....

## IF Formula Combination: Analysis Of The Month Value

Nov 23, 2009

I have to calculate the following: PREV = VND * NSF * GROWTH. But there can more than one choice for the values:

-VND: analysis of the month value; if this is empty then analysis the average; if the average is blank then returns “No data”
-NSF: analysis of the month value; if this is empty then analysis the average; if the average is blank then the value is 1
-GROWTH: analysis of the month value; if this is empty then analysis the average; if the average is blank then the value is 1

You can find in the excel file attached the formulas that are possible to exist.

## Projected Date Formula From A Combination Of A 2 Cells

Aug 3, 2009

I am trying to make the "Next Phase up DATE" cell automatically configure accorfing to the Issue date and the PHASE 1, PHASE 2, PHASE 3 Selection.

Phase 1 last for 14 days
Phase 2 last for 21 days
Phase 3 has no phase up date

So if I put 1 July 09 in Issued Date cell and select Phase 1, the "Next phase up date will be 15 July 2009. If 1 July 09 in Issued Date cell and select Phase 2, the "Next Phase up date will be 22 jul 09. If 1 July 09 in Issued Date cell and select Phase 3, the "Next Phase up date cell will be (N/A or something to that nature.) The Doortag portions are refrenced to the upper portions, so I only need the formula for (merged)CDE10 & (merged)CDE32

## Formula To Create Combination Of Text Values

May 22, 2014

I need a formula that can provide all possible alphabet combination in another cell .

example

COLUMN A A1 = RED
COLUMN B B1:B50 = BELOW COMBINATION / RESULT

RED
ERD
DRE
EDR
DER
RDE

## Can A Macro Write A Formula As An Array Formula

Jun 7, 2006

I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?

## IF Formula - Column To Show Text Value Dependent On Combination Of Three Columns

May 12, 2014

I have three columns that each have a drop down of text options; column A has three options, column B has three options and column C has two options. I want to have a fourth column that will show a text value dependent on the combination of the three columns.

It ends up being 18 variations so my IF formula just won't compute.

This is basically the format I have and works for a few values then just chucks up an error when I extend it to all 18 variations:

IF(AND(A1="text",B1="text2",C1="text3"),"show this text",
IF(AND(B1="other",B1="other2",C1="other3"),"then show this", ) etc etc

Any way I can get this to work for all 18 possible outcomes??

## Better Way To Write A Sum IF Formula

Nov 5, 2008

I basicaly need to look down column Q8 to Q52 for the falue "C19"
If that value is present - Then I need to look up the value of associated
with what is in its respective cell in column O8 to O52.

I have the formula that follows and it works, however it is huge. I have to repeat the IF,Vlookup 44 times. Just wondering if there is a better way.

Here is the formula I am using.

=SUM((IF(Q8="C19",(VLOOKUP(O8,clusterequipmentvalues,2,FALSE)),0)),
(IF(Q9="C19",(VLOOKUP(O9,clusterequipmentvalues,2,FALSE)),0)),
(IF(Q10="C19",(VLOOKUP(O10,clusterequipmentvalues,2,FALSE)),0)),
(IF(Q11="C19",(VLOOKUP(O11,clusterequipmentvalues,2,FALSE)),0)),
(IF(Q12="C19",(VLOOKUP(O12,clusterequipmentvalues,2,FALSE)),0)),
.... all the way to C52.

## Write Formula With Cap Of 125%

Jan 23, 2014

Write a formula with a cap of 125%

A
B
C
D

1
x
y
z

2
Commission
1000
1000
500

[Code] .......

How to write the formula to know how much commission with be paid out for x,y,z?

## Write This Formula

Jan 30, 2007

need to write this forwula for excel 1000((value from cell)+.256)=(((x+.314)1000)+9)1.0625. I would like to plug in a value from a cell and find the comparative value x. I am comparing the airfregiht cost between two carriers that have different surcharges for fuel, secuirty, and tax. One has no security or tax.

## Write This Worksheet Formula In VBA

Jan 1, 2010

I want to write the following worksheet function in VBA. In cell B1 : =IF(OR(A1={1,2,3,4,5,6,7,8,9,0}),"Yes","No").

## Write Formula Using IF / OR And AND Combined Together?

Sep 9, 2013

I am trying to write a formula using IF, OR & AND combined together, but I can't see to get my formula in the correct order. I have tried moving several sections around but I still keep getting an error.

IF(OR((A2="1A",A2="2A",A3="3A",AND(IF(OR(B2="Q4 2013",B2="Q1 2014"),"Y","N")

## How To Write Error Into Formula Bar

Mar 8, 2014

I'm looking to write a nested IF formula which will be dependant on certain cells that have errors in them (because those error cells have a formula in them which can't formulate becuase the numbers needed for that formula haven't been entered yet).

For example.

Cell A1=#DIV/0!. Cell B1=5.

In cell C1 I want to calculate the average between A1 & B1, but there is an error in A1.

I'm trying to figure out how to make a formula which ignores an error and gives me the value in the cell which doesn't have an error. But I also need it to calculate the average if both cells have a value in it.

## Write Flexible Formula

Jan 23, 2007

I have a sum field in excel which contains the formula to compute sum of certain cells. For eg. Cell C1 has value (C2+C4).

I have written a vba code to find cells whose value are to be included in the sum field (variables Lrow1,Lrow2 and Lcolumn1,Lcolumn2 contains the row and column number respectively). These cells may differ each time vba code runs. Can I write formula in the sum field based on Lrow and Lcolumn variables.

## Write Formula After Counting Rows

May 16, 2009

I have got mentioned below result after running a macro and I want mentioned below formula to be put after counting rows, because each time as per my query when I run macro result is different in rows as you can see in below table the formula I put in column F in rows 19, 20, 21, and 22.

ABCDEFG5DateD.IDAmountNODiff2TimeZone62-May-091001 500 50 - 1:50P-172-May-091002 10,000 120 - 1:15P-182-May-091003 2,000 100 - 1:15P-192-May-091111 300 40 - 1:50P-2102-May-091112 100 20 - 1:50P-2112-May-091113 234 100 - 1:50P-2122-May-091114 124 80 - 1:50P-2132-May-091211 380 3 - 1:00P-3142-May-091212 908 50 - 1:50P-315 Average1:14 16 Total Pins10191 17 18 ZoneRequest Count 19 P-13 20 P-24 21 P-32 22 Total9

Excel tables to the web >> Excel Jeanie HTML 4

## Write Double Interpolation Formula For Table?

Apr 16, 2014

Power.jpg I must write a double interpolation formula for the table.

## How Do I Write Formula To Show A Profit Margin

Dec 1, 2008

I need to work out the profit margin from goods i sell.

I have cost A2
Pack size A3
Net cost A4
Tax A5
Gross price A6

I need to work out the margin between A5 and A6.

## Write A Nested If Formula With Special Conditions

Aug 16, 2009

I thought that a nested if is what I needed, but now I don't know what I need I have explained in greater detail within my sample that's attached. Quote:....

I don't know how to write a function for what I need. Right now I have a function that says '= if \$b7=0,0,if(\$d7>\$d5,-1,1)), but I've found a problem. If there's more than one zero it throws off what I'm doing. I need a function that will ignore any zeros within column D and use the second to last number. For example:

The formula in E12 should read '=if(\$B12=0,0,if(\$D12>\$D8,-1,1))
The formula in E13 should read '=if(\$b13=0,0,if(\$D13>\$D9,-1,1))
The formula in E20 should read '=if(\$b20=0,0 ,if(\$d20>\$d12,-1,1))

I just don't know how to write this so that it will go back to the second to last number other than zero.

## Write Formula To Give Result If X Is Greater Than Y And Z?

Aug 7, 2012

If the total is >= to x but =(I10*0.9),"Over 90% spent",IF((M10+T10+AA10+AH10+AO10+AV10+BC10+BJ10)>=(I10*0.8)&(M10+T10+AA10+AH10+AO10+AV10+BC10+BJ10)

## How To Write Sum Formula For A Range In Last Empty Cell In Each Row

Sep 22, 2013

I have a spreadsheet. In this sheet the data is added daily so one column is increased everyday. The rows may also be increased. What I am trying to do is to sum the data in each row for a range of first cell in the row to the last non empty cell in that row and this has to be done for all the rows. So I thought the for loop would be useful for such requirement and I tried to write the following code. But unable to write the sum formula in the last empty cell in a row and I get the value error. The code is as follows.....

Sub sum_on_LastEmptyCell()
'find the last empty row in column A
lrow = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
'find the last empty column in a row

[Code] ........

Though one of my friend told that it can be done with "with and end with block but I am not aware of with and end with block.

It would be better if you tell me that how can I put the sum formula in my code. How can I use variables which return the row number and the column number in the sum range within the For loop because I want to put If Else condition for the calculations within the For loop i.e. if certain condition is true then I want this calculation to take place otherwise do something else. Moreover it will be easier for me to understand. Because I can use different formula based on different cells as well. Is there any way to do this?

## Rolling & Average (write A Single Formula That I Can Copy Down)

Mar 2, 2009

I've got a "Date" Column which has every day of every month, and next to that several columns with different types of data to correspond to a particular date.

To simplify this (because there are TONS of dates), I've made another "Date" column that displays only every 5th day. Now what I'd like to do of course is average the corresponding data over five days.

I don't know how to write a single formula that I can copy down that will do this.

I've attached a sample spreadsheet with only one month's data, the stuff I need a formula / code for is in red

## Write Vba Code Using Vlookup Formula Insert Pic From Picture Folder?

Mar 14, 2013

how to write vba code using vlookup formula insert pic from picture folder

## Write Formula That Will Count Number Of Unique Occurrences In Column?

Nov 2, 2011

I'm trying to write a formula that will count the number of unique occurrences in a column, if a specified value is found in a different column.

So I want to count the number of unique values in the "ID" column if let's say the text "NameA" appears in the "Name" column.

ID Name 12345

NameA
NameB
NameA 12346

## Excel 2003 :: Write Formula That Will Leave Cell Blank If Nothing Is Entered?

Sep 28, 2011

how to write a formula that will leave a cell blank if nothing is entered. I do not want it to show a 0 unless the cell entered is a 0.

In cell G16 - I am adding U46 and U58 together.

I do not have a problem when it is 1 cell - my formula works fine. When I have 2 cells added together, the formula does not work.

## To Write A Macro That Will Insert A Formula Directly Into The Cells In Column M Of My Worksheet

Aug 9, 2007

I am trying to write a macro that will insert a formula directly into the cells in column M of my worksheet.

The worksheet has about 3400 rows. When I run the macro it does not adjust the cell reference in the formula so I am getting the same value in all 3400 rows.

I am using the following ....

## Only Display Value If Combination In Two Columns Matches Combination In Two Other Columns

Jul 12, 2012

I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.

I need to only show the value from AUS!\$H\$2:\$H\$17 if the C2 & D2 combination are the same as the AUS!\$B\$2:AUS!\$B\$17 & \$AUS!\$C\$2:AUS!\$C\$17 combination.

## IF And OR Function Combination

Mar 20, 2009

I have 2 cells with numbers. In a 3rd cell I want to create a formula which looks at the 2 data cells and shows a value. The rules are the following: If C1 or C2 are bigger than Xthen C3=value1 else C3=value2. I have some basic excel knowledge but im not very familiar with functions. I'm using Excel 2007.

## Combination Of Sum Macro?

Feb 28, 2014

I need to consolidate several amount and make sure which of them falls into a sum of an amount.

I wonder if excel can find all the calculation in range A, and highlight those that equal to the cell D10 (example as per my attachment).

Calculation.xlsx

## Combination Without Repetition

Jan 16, 2010

how to create a list (I know how to calulate the count) of combinations without repetition when choosing 2,3,4 and 5 words from a set of 5 in Excel 2007.

e.g. Alpha,Bravo,Charlie,Delta,End

(AlphaBravo=BravoAlpha)

Choosing 2 = 10
Choosing 3 = 10
Choosing 4 = 5
Choosing 5 = 1