Excel Calculation - If Statement That Can Be Used On 2 Columns?

Oct 2, 2013

I need a If statement that can be used on 2 columns.

If there is a 1 in the first column I want to indicate if the 2nd column is 50% or greater with a number 1.

View 3 Replies


ADVERTISEMENT

Excel 2003 :: Multiple Columns With Similar Calculation?

Jun 27, 2014

I have Excel 2003 and use the following code for 50 consecutive columns, using each column's Row 1 as the multiplier. Each column has over 20,000 rows. It works, but I'd like to know if there's a quicker and / or neater way to do it.

Code:
Range("bo1").Copy
Range("bo2", Range("bo" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
Range("bp1").Copy
Range("bp2", Range("bp" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply

View 5 Replies View Related

Excel 2010 :: Pivot Table For Financial Statement With Months As Columns

Jul 4, 2012

Excel 2010. I set up a trial balance as following:

1/31/2000 2/28/2000 3/31/2000 ------- 12/31/2012
Acct # 1
Acct # 2
Acct # 3
Acct # 4

I was able to use pivot table to set up the balance sheet and income statement.

1. now each month is a field which I can choose to add to the report, but the field list is getting too long (and growing). Is there a way I can insert 1 field (for the user to input the exact date) or 2 fields (to input year and month) and the pivot table can pick up the input and refresh either automatically or manually.

2. I looked into the "group" function in pivot table, but how do I group the above data in quarterly and yearly fashion so the user can get those information quickly?

View 1 Replies View Related

IF/THEN Statement With Calculation

Nov 6, 2008

I would like to perform what seems like a simple calculation: =IF C1>0, THEN A1-C1, IF C1=0, THEN DO NOTHING. How would this look as a formula?

View 4 Replies View Related

Combining IF Statement With Calculation?

Nov 4, 2013

I am putting together a spreadsheet that needs to perform different calculations based on the information that is plugged in to the spreadsheet. Right now, I have a simple formula for my final product, which is:
=G2-E3-F3+C3+D3

But, if the variables change, then I have another cell with this formula:
=IF(OR(C4=0,D4=0),"",SUM(G2,H3))

Now, my first problem is that I need to get the information regarding D4 out of the above formula. Someone wrote the formula below to combine the information in cell C3 & D3, so I no longer need the reference to cell D4 in the above formula. How to remove the D4 references without getting an error.

You will notice that the 2nd formula has cell C4. This cell has an IF statement in it. The statement is as follows: =SUM(IF(C3=0.875,D3-0.625,D3))

Basically, what I need to do is to combine the first & second formulas on this page. How to do that.

View 2 Replies View Related

IF Statement With Date Calculation Within

Jul 26, 2007

I have several columns as so:

B - Patient Surname
C - First Surname
D - Postcode
E - Date
F - Results of If Statement (Days Difference)

I am looking to comapre b, c, d with teh previous cells to make sure I am looking at the same person hcen the AND statement. If they all match then if subtracts the latest date (column E) from an ealier date for the same person. If the person is different it will return a 0. If the date is the same ti will return a 0.

I have done this so far - =IF(AND(B2=B1,C2=C1, D2=D1),(E2-E1),0)

What I am struggling to do is if the number of days (E2-E1) is 0-3 then for the result to show the text "Less then 3". If the value is 4 - 7 then to have it read "Less then 7", anything over 7 to read "OVer 7".

View 8 Replies View Related

If Statement - Need To Stop Today Date Calculation

Feb 4, 2014

This formula is being used in a vacation time spreadsheet. So after each paycheck the if statement is applied and need to calculate the accumulation for that week based on if the person has worked 5 yrs or less. I need to stop the today's date calculation in the if statement after it is used. So that each time the spreadsheet is opened the values are a fixed number not changed based on the current date. However if the if statement is pasted into the next pay week it will calculate based on the actual date.

It is calculating is the following:

C4 = 5yrs Anniversary Date of Hire
3.08= less than 5 yrs
4.62= more than 5 yrs

=IF(($C$4)<=TODAY(),4.62,3.08)

[URL] ......

View 1 Replies View Related

Countif Statement - Calculation Performed For Range Component

Dec 14, 2012

Is it possible to insert a formula or have a calculation performed for the range component when using the countif statement.

I am trying to take the entries in the rows in column B and divide its corresponding row in column A and then count how many meet a certain criteria.

View 1 Replies View Related

Design If Statement That Refers To A Range Of Cells On Calculation Sheet

Mar 21, 2014

I'm trying to design an if statement that refers to a range of cells on a calculation sheet to perform one calculation on TRUE; another on FALSE

=IF(A22='Calculation Sheet'!A27:A36,B22*100/16,B22*100/8)

When I view the formula in Function Arguments window, Value_if_true = 1043.75 & Value_if_false = 2087.5 <- these are correct

IF Func Arg.jpg

When I view the cell in the spreadsheet, system displays #VALUE!

When I view Evaluate Formula window, it seems Excel doesn't like the text.

IF Form Eval.jpg

View 5 Replies View Related

Excel 2010 :: Use One Set Statement To Set Second Statement?

Feb 14, 2012

I am attempting to use a previously Set variable as part of the next Set statement, pretty unsuccessfully at present.

My purpose is trying to look up

Code:
tb_SelJobID.Value

from a userform in Col Z then look across the row to Cols D,I,N,S & W (different types of work) to see if

Code:
TbSelYr.Value

matches the year selected then insert a formula in the row to the left. Then loop down to the FinalRow.

Currently my Set Found1 statement does not recognise my Found10 value. I know it will be my syntax as it always is. I have cut down the following code to display where the problem areas are, Found1 thru 5.

Code:

Sub CmdGo3_Click()
Dim Row As Range
Dim FinalRow As Long
Dim Found1, Found2, Found3, Found4, Found5, Found10 As Range
Application.ScreenUpdating = False

[code]....

Windows 7 with Excel 2010

View 9 Replies View Related

Sorting Columns - Select Lowest For Calculation

May 12, 2014

I am trying to find a formula that will look at the figures in four columns and then select the lowest for the calculation.

Basically what I am doing is getting venders prices and placing them in the column. The cheapest is then selected.

Showing which vender is used would be a bonus.

View 1 Replies View Related

IF Statement On Multiple Columns

Jul 28, 2009

I have a formula that looks at 5 columns. If any one of these 5 columns meets the specified criteria, it will "Fail" the row. What I want is, when the row is failed, to display the column that was failed. Well I have that part under control using the following formula.

View 2 Replies View Related

2-Condition Statement - 2 Different Columns

Oct 28, 2009

Rows:

Each row contains a new client (a new entry)

Columns:Clients first and last name.
Case number
Case type - Case type can be AAA, BBB, CCC, DDD, EEE, (and many more).
Case outcome - Case outcome can be Granted (G) or Denied (D)

A case can be of type AAA, and be Granted.
A case can be of type MMM, and be Denied.
A case can be of type ZZZ, and be Denied.
Etc...

I am trying to keep track of how many cases of each type have been granted and denied. If you look at the file i attached, you will see that the table is a list of clients, with different case types and different outcomes to their cases.

The problem i am having is that i need a way to have a two-condition statement as such:

if case type is "AAA" and case outcome is "G", then add 1 to a "AAA GRANTED" column on another sheet.,....................

View 3 Replies View Related

Hide Columns With If / Then Statement

Jun 23, 2013

I'm attempting to put code in that will hide 2 individual columns when a particular cell in another column has no value in it. The cell, however, has a formula in it, and I'm not sure if that is my issue.

Essentially, if the formula in A32 returns a value, I need columns M and P to Hide. If it doesn't return a value, I need them to stay put. The value returning in A32 is a number, but it can be treated as text as it is an ID number and is not needed for any calculation.

Here's how I have it set up now:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A32") "" Then
Columns("M","P").EntireColumn.Hidden = True
Else
Columns("M","P").EntireColumn.Hidden = False
End If
End Sub

It's not working - nothing is happening.

View 5 Replies View Related

MAX And IF Statement On Multiple Columns

Jun 30, 2014

I was wondering if there is a more beautiful way of this formula

Code:
{=MAX(IF(IF(B14=A1:A12, B1:B12)>IF(B14=A1:A12, C1:C12),IF(B14=A1:A12, B1:B12),IF(B14=A1:A12, C1:C12)))}

A
B
C

1
A
10
10

[Code] ...

Highest B is 80

View 7 Replies View Related

Comparing Dates In Two Columns Using IF AND OR Statement

Apr 14, 2014

I'm trying to determine if one date meets the criteria to be considered 'on time'. I have two columns: estimated completion date and completion date. I want to compare the completion date to the estimated completion date and if completion date is <= completion date but is NOT = 1/1/2099 then I met my date. I've tried this several different ways and problem is I cannot make it work.

When estimated completion date is 1/1/2099 'on time' should be 'no'
When estimated completion date is not 1/1/2099 but is greater than completion date 'on time' should be 'no'
When completion date is <= estimated completion date and estimated completion date is not equal to 1/1/2099 'on time' should be 'yes'

And I have a null date to deal with also but if I can satisfy the first 3 criteria I can manipulate the null values manually.

View 9 Replies View Related

Excel Calculation System With Two Factors

May 8, 2014

I am making an Excel calculation system where I have two factors (weight and opening length), which is essential for which lift there is possible to use.

There are 8 models each of which may have some limitations in terms of weight, and the opening length:

ModelPushing/pulling Force (KG)Stroke Length (mm)
HCV 130 350
HCV 230 600
HCV 330 800
HCV 430 1000
HCV 550 350
HCV 650 600
HCV 750 800
HCV 850 1000

What I am looking for is a formula, that when you enter numbers in two cells "Weight" and "Opening distance" of what their platform is to meet, then all the models that have the potential to accomplish this task pops up.

Example: A client writing a weight of = 40 kg and opening length of = 450 mm. The formula should then show which model that meets the requirements.

In this case: Models that meet the requirements for the lift.

ModelPushing/pulling Force (KG)Stroke Length (mm)
HCV 650 600
HCV 750 800
HCV 850 1000

View 5 Replies View Related

Excel 2007 :: Calculation Of TAT Between Two Dates

May 30, 2014

I have a Billing Data where i want to Calculate TAT between two dates in Excel 2007.. I received the Invoice from Vendors on specfic date but due to discrepances, i rejected the invoice, after few date i received a rectified Invoice from Vendor... so how can i calculate the TAT from 1st Receipt to Rejection..

Column A Column B Column C Column D Column E Column F
(Receipt Date) (Rejected Date) (Re-Receipt Date) (Rejected Date) (Final Receipt Date) (Formula for Finding
Rejection TAT)

E.g : In Column F, TAT to be calculated from 1st Receipt Date to Rejection Date, but if i received the rectified bill & after inputing the Re Receipt Date in Column C, then the Cell in Column F will remain blank, but again if it is rejected due to some error, & after mention the 2nd Rejection Date in Column D, then the TAT to be calculated from 1st receipt to 2nd Rejection (Column D), but if i received the Final Rectifed Invoice & after mentioning the Receipt Date in Column E, then the Cell in Column F will remain blank.. but pls note, if the invoice is not rejected the, the Cell in Column F will remain blank..

I have tried IF Forumula but in vain.. i can only nest 1st receipt to 1st rejection i.e : =IF(AB6= "", "",(TODAY()-AB6))

View 10 Replies View Related

Calculation Using Macro ( MS Excel 2007 )

Jan 6, 2008

I want to do simple calculation using Macro in MS Excel 2007..

All the data is in Sheet1 ( Which is 13a ), the calculation is in Sheet2 ( Which is work ) and the result of the calculation will come out in Sheet3 ( Which is result )..

Here is my coding..

View 12 Replies View Related

Distance To Default Calculation Using Excel?

Mar 16, 2013

how to to calculate the distance to default using excel. Or send e some previos work sheets that i can follow how it is done

View 1 Replies View Related

IF Statement Logic In Columns - True / False

Mar 31, 2014

Column A: T/F
Column B: True/False

Here's what I want in a sentence: If one of the cells in grouped row is "T", column B = True, if not then column B = False.
(row groups are separated by a blank row)

Example)
Col A,Col B
TTRUE
FTRUE

[Code] ....

I came up with following formula:

=IF(MATCH(TRUE,INDEX(ISBLANK(A1:A17),0),0)>MATCH("T",A1:A17,0),TRUE,"")

It checks to see if blank cell is found before "T" but it doesn't look rows above.

inincubus.grouprows-1.xlsx

View 3 Replies View Related

Excel Formula / VBA Code For Time Calculation

Aug 14, 2012

I am calculating the employees time and need the time spent to be categorized as follows

Monday to Friday
06:00 to 09:00 is OT
09:00 to 17:00 is BASIC
17:00 to 00:00 is OT
00:00 to 06:00 is PREMIUM

A2 =Date= 31-07-12
B2 =Start Time = 03:00
C2 = End Time = 23:00
D2 = c2-b2 = 20 hours formatted as [h]

there are situations when end date and time is the next day

E2 = 8 hours
F2 = 10 hours
G2 = 3 hours

Saturday
07:00 to midnight is OT
Midnight to 07:00 is premium

Sunday
all the time is premium

View 1 Replies View Related

Excel 2010 :: Calculation Of Leave Days?

Jan 5, 2013

Working on Excel 2010

Need a formula to calculate the number of leave days.

C3 - Start Date, e.g. 01/07/2011
D3 - Calculation date, e.g. 31/12/2012
E3 - Number of months - ??????? Forumal need from 01/07/2011 to 31/12/2012
F3 - Days per month, e.g. 1.25
G3 - E3*F3

View 6 Replies View Related

MS Excel: Standard Deviation Conditional Calculation

Apr 17, 2009

Can someone please help me with my query?

I have a large database and would like to calculate standard deviation based on a condition "Yes" or "No".

Shown below is a small layout from my database: ....

View 6 Replies View Related

Adding Multiple Columns That Have Price Range Statement?

Feb 28, 2014

A
B
C
D

1
$10
$2.95 to $11.00
$4.50 to $19.00
$17.45 to $40.00

Is there a formula that will add column A to the left and right sides of columns B and C to give me an overall answer range in column D?

View 2 Replies View Related

Excel 2010 :: Formatting Military Times And Calculation

Jul 2, 2014

I work for a freight company and have been trying to create a macro for a spreadsheet that I have to create every single day. I pull a report from a program called the AS400 and it sends me an excel spreadsheet with information about certain shipments such as close time and arrive time (which are in military time such as 16:00) along with driver names.

Anyways what I need to accomplish is to highlight the entire rows font in red if the driver arrived an hour or more past there close time. When I get the spreadsheet the military times are not showing in the HH:MM format under format cells. So I select the HH:MM format and try to do a formula such as Arrive time cell (M1) - close time cell (L1) to get the time difference it shows as #VALUE!. I've tried to convert it to different time formats and nothing works. The values just stay the same and will not allow me to do calculations with them.

At first I thought this was because the program was sending those values as text. I tried to use the date>text to columns>finish button on the columns but did not work. I've tried to remove the":" then use the HH:MM cell format but still wont work. I've even tried a time value formula to try to convert the values such as =TIMEVALUE(LEFT(A1,2)&”:”&RIGHT(A1,2)).

But nothing works it will not allow me to add or subtract from the time values no matter what I do. I am running excel 2010 on Windows XP the english version.

View 9 Replies View Related

Excel 2007 :: Differences Between PEMDAS Calculation And Evaluate Formula

Apr 16, 2014

Using excel 2007 and I noticed some differences between how a caluation is done via PEMDAS (as I understand it) and evaluating vai excel.

The formula is just an example and doesnt relate to anything as such.

The formula is: 2*6+3-4/2-5+20/5*3+50

using [URL] the formula show the calculation as I would have calculated.

Manual Order.png

When using excel it does it in a different order

Excel Order.png

Any reason why?

View 1 Replies View Related

Excel 2010 :: Average Calculation For Given Date And Time Range

Jul 9, 2014

Please find the attached 2010 version excel file <For a given date Time Range average .xlsx>.

I want to calculate the average value (Column C) for the given date (Column A) and given time range (Column B).

Values to be plotted in the color cells yellow and light pink / magenta.

Voting Results.xlsx‎

View 4 Replies View Related

Excel Template To Define Process And Present Calculation Methodology?

Jul 31, 2013

I have been given a task where I need to define 3-4 process and present the various formulas etc.. that lay out the calculation methodology. I was looking for some ready to use excel templates that have an index page, hyperlinks to different tabs etc...that I can customize to my needs.

The idea is to be able to build a user friendly excel process document that will have all the data from our various (3-4) reports that are interlinked. It will serve as a reference point for existing employees and new hires.

View 2 Replies View Related

Excel 2010 :: Condensing Dim Statement

May 7, 2013

Windows 8, Excel 2010,

What is a condensed way of saying;

Dim x As Integer
Dim y As Integer
Dim z1 As Integer
Dim z2 As Integer
Dim z3 As Integer
Dim z4 As Integer
Dim AA As Integer

View 9 Replies View Related







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