Nesting IF THEN Calculation Solving For Cells That End With Or Include

May 5, 2009

Want to write an if then that states if a cell ends with (or includes) .com or .org then = "", if not then = "1".

View 3 Replies


ADVERTISEMENT

Nesting Calculations Dependent On Previous Calculation

Oct 9, 2009

how to determine which "Gate" an employee has attained for product sales.

Example: Employee A sells each of the 4 products and has made or exceeded Gate 1, the result should show Gate 1. If they make or exceed each Gate 2 the result should show Gate 2 not Gate 1. Etc..

Note: All 4 products must meet or exceed a Gate to be valid for that Gate. So if employee A sells to Gate 3 level on all products but one, their Gate will be at the level of that one lower product. Clear as mud?

Below is my matrix shhowing the levels of each Gate.

Gate 1
Gate 2
Gate 3
Product 1
11 to 12 13 to 14 15 and Up Product 2
12 to 15 16 to 19 20 and Up Product 3
1 2 3 and Up Product 4
18 to 21 22 to 25 26 and Up

View 9 Replies View Related

Nesting OR, AND Within IF: Comparison Amongst Several Cells And Returning A 1 If True

Dec 24, 2008

I'm trying to do a comparison amongst several cells and returning a 1 if true. if D25 is blank or does not equal Yes, Mixed or SP3, and if both F25 and G25 are blank, the output the value 1. First try:

=IF(AND(D21="",D21<>"Mixed",F21="",G21=""),1,"") - This seems to ignore the Mixed and outputs a 1 even if D21=Mixed.

Second try:

=IF(AND(OR(D25<>"Yes",D25<>"Mixed",D25<>"SP3"),F25="",G25=""),1,"") - same as above, if D25=Mixed then value of the cell is 1.

View 2 Replies View Related

Editing Macro To Include Multiple Rows And Include Text Formatting For Leading Zeros

Apr 2, 2014

I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.

View 10 Replies View Related

Solving Equations Using VBA

Oct 24, 2013

I am given the function
y = a x + b

And I am supposed to ask the user to enter a value for a and b twice

So I could have 2 equations and solve them

The problem is I am not sure how to solve the 2 equations using VBA?

View 3 Replies View Related

Solving Polynomial Equations

Feb 16, 2007

I don't actually own Excel as yet - but I'm wondering if someone could tell me whether Excel is capable of solving what I'm told are 'polynomial equations'. An example of the sort of equation I hope to solve is: d/(1.18 + d) + d/(1.86 + d) + d/(9 + d) + d/(8.8 + d) = 2
d=? {I happen to know d = 3.65(approx) in this example btw}

Often there will be many more than four terms...

View 6 Replies View Related

Solving System Of 3 Nonlinear Equations

Feb 12, 2013

I have a system of 3 equations with 3 unknowns that I would like to solve in Excel.

The equations are
24.792 = A*e^(B/(100-C))
9.857 = A*e^(B/(70-C))
4.590 = A*e^(B/(40-C))

Where e is the exponential function.

View 7 Replies View Related

Solving Algebra (Equation) In Excel

Jun 5, 2003

How to do the following, which solves the equation, in Excel.

LH
W1
W2

(LH-X)/(LH+X)=W1/W2
(6-x)/(6+x)=.6/1.25
6*1.25-x*1.25=6*.6+x*.6
7.5-1.25x=3.6+.6x
7.5-3.6=1.85x
3.9/1.85=x

x will equal 2.108..... in this case

View 9 Replies View Related

How To Not Include Blank Cells In IF Function

Apr 14, 2014

If have very simple =IF function, =IF(F4=H4;1;0) but don't wan't the 'value is true' value when both cells are empty, how do I need to change the function so it states 0?

View 3 Replies View Related

Change Array To Include New Cells

Aug 14, 2009

I am having a ton of trouble with this. I am trying to add a single text row in between existing data. I have tried multiple loops to try to place an array into Column G, but I cannot figure it out. Is there some coding that will produce the sort of result below in column G?

View 6 Replies View Related

Formulas That Include Blank Cells

Oct 9, 2008

I can't figure out how to make this formula work because I need to use cell AG46 and it contains an IF formula that leaves the cell blank if the sum = 0. I keep getting the #VALUE! error. How do you create a formula using a cell that has an IF formula that could leave the cell blank?

Basically this is what I need in cell I12:
=sum(a12)-(ag46*8)

View 2 Replies View Related

Calculate Range Without Cells That Include Zero?

Jun 20, 2013

A range of cells is populated by numbers and formatted as such, some of those cells contain zeros. How do you calculate a range of cells without the zeros in the range? The zeros skew the results.

View 5 Replies View Related

How Do You Include Specific Cells In The Email

Jan 14, 2008

I have the code to send a email based on the due date. The email will include the Due date for the purpose below of today 1/14/08 in the message. I cannot figure out how to use Offset in the email message portion to include

The Last and First names along with the Exp Date in the respective column.

Example below: row 3--due date shows up in email message, what would the Offset code be to include Grimm, Paul, Exp Date3, 1/14/08 in the message?

Then the same for row 2, the due date 1/14/08 which the Offset information would be different than line 3?

Since the due date can be in various places how do i still get the Last First and Exp Date"x" included in the message of the email? ....

View 9 Replies View Related

Dim Array To Include All Used Cells In Column

Jan 1, 2009

I m trying to use arrays in the wrong way. I simply want to create an array from values in a column and perform calculations on each element and store the results for each element. I therefor need a 2d array. I also dont know the number of values in the column and therefor dont know the number of elements that will be in the array. How would I declare this array. Ive tried Dim MyArr(1 To ActiveSheet.UsedRange.Rows.Count, 1 To 2), but that returns an error - 'Constant expression required'.

View 6 Replies View Related

Analytically Solving Equations To Establish Physical Value

Apr 4, 2014

I am trying to analytically solve an equation to establish a physical value. Unfortunately, it's difficult to do the algebra, so it needs to be done this way.

I have an experimental value for this in "J2", so what I want to do is change F2 to that "I2" is equal to "J2"

Is it possible to get excel to match these values - I have about 200 different cells of this?

The formula is in cell "I2"
=B2*((1-(C2/D2)*((F2/G2)^1.2))^-1.5125)

View 11 Replies View Related

Solving Multiplication Of Two Matrices With Entries Decimal

Mar 16, 2014

Problem solving multiplication of two matrices with entries decimal in vba code:

Code:
Option Explicit

Public MatrixA(30, 30) As Long
Public MatrixB(30, 30) As Long
Public MatrixC(30, 30) As Long
Public RowA As Integer
Public ColA As Integer
Public RowB As Integer
Public ColB As Integer

Function ProperMaticesSizes() As Boolean

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

View 1 Replies View Related

Averaging Data Not To Include Null / Zero Cells

Mar 10, 2014

I am having difficulty trying to solution this:

Row 1 = Dates e.g. 1-Mar - 31-Mar
Row 2 = Day of Week e.g 1-Mar(B1) = Sat(B2) through 31-Mar ending at (AF)

Column A has hourly time intervals
A3 = 0:00
A4 = 1:00 etc to 23:00

Numbers fall into cells by date/day and interval up to today 10-Mar

What I am trying to do at is average the days separated by Weekdays and weekends..so the formula at AG for interval for weekday would be =AVERAGE(D3:H3,K3:O3,R3:V3,Y3:AC3,AF3) weekends (Column AH)would be =AVERAGE(B3:C3,I3:J3,P3:Q3,W3:X3,AD3:AE3)

I want to average the weekdays and weekend numbers without having to group the weekdays and weekends in a custom sort in the final column. That way when I add the data every day, it auto calculates in AG and AH. If I do it as it shows above, the AVG is skewed due to the blank cells.

Attached a sample worksheet.

View 4 Replies View Related

How To Count Average Of Selected Cells And Not Include Zero

Jul 7, 2011

average i want to count.

Say i want to count the average from differnet selected cells say

cell A1,B4,C9,D10,E12

and some of the cells have the value 0 in them, so i don't want the formula to include these as long as they have the value 0.

I have tried to divide with a sumproduct and i can get i to work if it is cells next to each other like A1:A20, but not if i want to do it with different selected cells

View 6 Replies View Related

Formula To Include Multiple Requirements In Different Cells

Aug 30, 2013

I would like a cell to show either OK or No depending on the requirements of three other cells as below

G4 is the cell to show a value. For OK a date in G6 must be more than todays date, a tick must be in F6 and a date in D6 must also be more than todays date.

I can get G4 to do each section individually i.e. (A4 has todays date in it, updating automaticaly)

=IF((G6+365)

View 2 Replies View Related

Calculate Temperature Values Directly For 10th Iteration Without Solving Each?

Nov 13, 2013

I had 4 initial temperature values and after some time i got new temperature values. Now for calculating 2nd iteration i need to use new calculated values and for third iteration values from 2nd iteration is to be used and so on.

How to calculate temperature values directly for 10th iteration without solving each iteration?

View 2 Replies View Related

Copy Cells (include Conditional Formatting) And Paste In MSPaint?

Oct 29, 2012

I want to copy cells (include conditional formatting) and paste in mspaint How can i write this code ?

View 9 Replies View Related

Format Cells Based Upon Being Outside Scope Of Calculation Of 2 Cells

Feb 6, 2008

I am comparing a set of common variables across 2 sets of independent financial data. When comparing a variable across the 2 sets e.g. profit I want to colour a neighbouring cell based on the size of the difference. For example if the difference between the two values is within plus or minus 1 make it green, plus or minus 5 red etc.

View 5 Replies View Related

Filter Column A To Include Multiple Cells Of Data In Column D?

Mar 31, 2014

Is there a way to filter column A but ensure it includes multiple lines of data in a different column.

I have attached an example of what I am looking for:

I need to filter by community but keep all the information listed in columns, B, C, & D.
So when I filter to Thunder Bay I would still see the 5 members listed in column D.

I realize the easy solution is to fill all the empty cells in column A to pick up the data in the other columns, but I do not want to Fill all the empty cells to include the name "Thunder Bay" (not the look I want for this spreadsheet).

View 2 Replies View Related

Removing Date And Seconds From Cells That Include Date And Time

Apr 30, 2014

I have only one column (A) with thousands of cell entries. Each Cell contains a date and time. (x/xx/xxxx 12:34:56)

For example, Cell A1 = 1/24/13 18:45:22

I want the new cells to only have the time, but have the seconds removed (and not simply hide the seconds). This is because I'll need to run Conditional Formatting to find every cell with a similar time - but I only need similar times within the same minute...it doesn't matter if the seconds are off.

The new cell should just be A1 = 18:45

The real reason I need this is because I need to find every entry that has the same time (with hour and minutes), which I figure I'd use conditional formatting for. My problem is that many entries have the same time but the seconds are off.

Example:
1/22/13 12:23:11
1/24/13 18:45:22
1/24/13 18:45:36
1/26/13 22:33:41

In this example, I'd want Conditional Formatting to highlight cell A2 and A3 because they have the same time of 18:45 (not including the seconds). I figured the best way to go about this would be to remove the date and remove the seconds, and run the search that way. However, any way to accomplish goal without deleting the date and seconds would be just as useful.

View 4 Replies View Related

Time Calculation Between Two Cells?

Feb 14, 2014

I have attached excel sheet. Column A consists of List of LAN numbers, Column B consists of List of Documents tagged & Column C consists of time at which particular document is tagged.

I need to calculate the time taken to tag all the documents in Lan1/Lan2/Lan3 in Column D. (I can ignore the date by doing text to columns).

View 3 Replies View Related

Calculation Based On % Of Cells

Oct 29, 2008

I need a formula that I can place in, lets say, Cell A1 that will reference Cells B1, B2, B3 and C1, C2, C3. These all have numbers & %'s in them. However, I want to "weight" the B cells by the percentages in C cells. The problem I am having is that when I change the %'s nothing is changing in the output...almost like I was doing nothing but averaging B1:B3 without the % to adjust.

This is how I envision it, but my calculation may be wrong of course :
A1 = average(sum(B1,B2,B3)*C1, sum(B1,B2,B3)*C2, sum(B1,B2,B3)*C3)

The Goal:

This would allow me to change the output depending on the percentage. So if I am looking at days to an appointment and the B cells represent areas of a city: B1 = 10, B2 = 5, B3 = 2 the average would be 5.6, however, I know that B1 city has more "weight" to it with our customer base so I want B1 to be closer to what will show as days out (which is obviously important as 10 days is not good). So I would assign C1 = 60%, C2 = 30%, C3 = 10%. This should pull the average more to 7 or 8 as it would lean more towards the B1 value. I hope this makes sense.

I did try going more of a route where I did something like =average(B1*C1,B2*C2,B3*C3) but that lowered all the original values instead and averaged them instead of pushing the number more towards the higher weighted % of B1 cell value. I can send an example spreadsheet if I am losing anyone.

View 8 Replies View Related

Filling Cells According To A Calculation

Jun 29, 2008

I have a spreadsheet that is designed to aid a production department to complete and track their orders. I need two things from this.

1) I have an order of 500 baskets. Baskets are stacked, but the heights vary from 16 to 20. I need the spreadsheet to be able to work this out for me.
Example:
500 baskets 20 high = 25 stacks.
500 baskets 16 high = 31 stacks and 4 baskets.

Excel would work the last equation out as 31.25. How can I get excel to use the remainder (in this case 0.25) to work out the required number of baskets?

2) Using the above, I need the sheet to fill out a certain area with information. So, if the order is 500 baskets at 20 high, I need 25 of the cells in the specific area to display the number 20. Likewise, if the order is 500 baskets at 16 high, I need 31 cells to display 16 and the remaining cell to show 4.

View 9 Replies View Related

Determine Another Cells Value Before Calculation

Oct 19, 2007

I need to create a macro that will make a cell only run its formula when an associated cell has something in it that will trigger the function.

View 2 Replies View Related

Compare Cells & Value To Others Cells For Calculation

Mar 7, 2008

I am trying to code for the following conditions and will like to know how I can embed these conditions into one statement.

IF(AND(X>365, A>B), C*D
IF(AND(X>365, A<B), E*F

If neither of the conditions apply, then 0.

View 5 Replies View Related

As Soon As Sum In 10 Cells Crosses Value 20 Stop Calculation

Apr 29, 2014

I have a worksheet in which i have rows with yellow colour. In that sheet I Have written formula in that cells & rows. But i want that as soon as the total (SUM) of yellow coloured row crosses value 20 ALL THE ROW CELLS SHOULD BE ZERO. How can i do this

I am attaching sample sheet. EXAMPLE15.xlsx

View 2 Replies View Related







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