Formula For A Cell Contains Specific Character Then Multiply By Another Cell

Feb 17, 2014

Can I get a formula for the below specification.

if a cell contains character A THEN multiply by cell B * 0.02,if a cell contains character B THEN multiply by cell B * 0.05,if a cell contains character C THEN multiply by cell B * 0.010

View 1 Replies


ADVERTISEMENT

Returning Value If A Cell Contains Specific Character?

Feb 25, 2014

Essentially what I'm trying to accomplish is import a file from Quickbooks and determine whether it was a Credit Card, Check or Invoice based on the account number.

For example:

45-12345 should return the value CC in an adjacent cell because it contains the "-" character.
I0123456 should return the value INV in an adjacent cell because it contains the "I" character.
01234567 should return the value ACH in an adjacent cell because it doesn't contain either character.

I tried using VLookup, but I can't figure out how to write a formula for when it just contains a character, only if it matches it or is text/number.

View 4 Replies View Related

Find Specific Character In A Cell

Dec 9, 2009

I am trying to put an IF statement together. If the 7th character from the left is 5 it must show M, and If 0(zero), it must show F

View 2 Replies View Related

Find Cell That Contains Specific Character?

Jul 17, 2012

Cells A1:A5 all have a vlookup formula that will pull one of the following into it:

1. The word "NONE"
2. #N/A error since it doesn't exist in the table
3. A text value that has the format 13.55.46.91

I want a formula that will look at these 5 cells and give me #3. That #3 value can only appear once in the 5 cells in any of those 5. The other 4 cells will contain either 1 or 2.

3 always has length equal to 11. It always has periods in those places. The digits do change.

View 3 Replies View Related

Count Cell Containing A Specific Character

Mar 3, 2008

I have a row of cells that contain a single letter: W,C,O.

I need to provide a count of the number of occurancies of each letter. So I have a cell that shows me the total W's, a cell showing me total C's and another showing total O's.

Whats easiest way to do this?

I know its not ideal, but on this occassion ive no desire to add and hide another column.

View 9 Replies View Related

How To Show Position Of Specific Character In Cell

Mar 9, 2014

How to find and show position number? I try merged two function, but doesn't work

1º=FIND("0";B2)
2º=FIND(CHAR(1);SUBSTITUTE(B2;"0";CHAR(1);2))
Number
Position

[Code].....

View 5 Replies View Related

Multiply Dependent One Cell Formula

Aug 8, 2006

All to occur in cell G2

Condition 1.
If F2 is > 0 then multiply F2 by D2.

Condition 2 in the same cell.
If the product of F2 multiplied by D2 is > E2 AND the sum of F2 and F1
multiplied by D2 is > 0, then add the product of D2 multiplied by the sum of
F1 and F2 to E2, otherwise input E2

Condition 3 in the same cell.
If the product of F2 multiplied D2 is < E2 BUT the sum of F2 and F1
multiplied by D2 is > 0, then input the product of D2 multiplied by the sum
of F1 and F2.

Condition 4 in the same cell.
If F2 < 0, and the sum of sum of F2 and F1 multiplied by D2 is < 0, then 0.

This is the best way I could think of the write out my intention. I think if
I can get the above scenario into a one cell formula that should resolve my
pain.

View 11 Replies View Related

Conditional Formatting - Highlight Cell Containing Specific Character Within Text

Jul 26, 2013

I have an excel spread sheet & I wish to highlight any cell that contains any of the following characters with in a string of text.

/ : * " < > ? |

E.g. a cell in the spread sheet containing the text "Is this a Question?" would be highlighted.

I have tried to use - 'Format only cells that contain' > Specific Text > Containing > for each of the characters and this does not work.

When I do this any cell containing any text is highlighted.

In addition there is formatting on any cell > 60 characters which turns the cell red and this works fine.

What would happen if (when I get the problem above resolved), a cell is > than 60 characters & contains a character listed above?

Does 1 formatting take precedent over another?

(Not critical to know the answer to the additional question, just curious - as long as it is highlighted one colour or another then no problem.)

View 3 Replies View Related

Replace Character Used In Formula Where Formula References Cell

Feb 23, 2008

I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url.

i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6

this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads

I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire

View 9 Replies View Related

Formula To Remove All But The Last Of The Same Character In A Cell

Aug 21, 2009

I have data in cell A1 and A2 which looks like below

SECTOR - 11, HIRAN MAGRI, - 313001
MAIN BRANCH, 30-15-171 DABA GARDENS, NEAR SARASWATI PARK,-530020,

In cell A1 the number of hypens (-) are 2
In cell A2 the number of - are 3

What I am looking for is a formula which can remove all additional - except the last one. Therefore the result of the formula should be

SECTOR 11, HIRAN MAGRI, - 313001
MAIN BRANCH, 30 15 171 DABA GARDENS, NEAR SARASWATI PARK,-530020

View 9 Replies View Related

Use Dynamic Formula Driven Text From Cell To Identify And Extract From Specific Cell?

Dec 21, 2013

I paste new data into a sheet of a monthly report I prepare. For this sheet, the # of data rows change (and is unpredictable) every month. I need the value inside a specific cell that dynamcially moves up and down based on the # of rows for that month (because it's below the rows of data).

So I made a formula to identify the exact cell # every month.

Example:

This month the exact cell is F255 in the "Refi" sheet.

So my formula in the "Summary" sheet cell A1 first finds the cell row # only (255) and since it's always column F, in B1 I have

VB:
="F" & (A1)

This outputs "F255" in B1, successfully identifying the target cell.

Now how do I write a formula in C1 to grab the value from whatever cell is named in B1. (For this month, the value in cell F255 from the "Refi" sheet)

View 7 Replies View Related

Multiply From Data In Cell On Sheet2 From Cell On Sheet1

Sep 18, 2012

I have a value in a cell in Sheet2 (in this case its price per ft), on sheet1 is where I would input the number of feet. So can I multiply from data in cell on Sheet2 from cell on Sheet1and if so....

View 6 Replies View Related

Multiply Cell Without Copying Cell Formation

May 16, 2014

I want to use the little dot in down right corner of the cell to copy the cell content and the formula correctly.

But now the problem: every time I use it also copies the cell formation.... Is there a way that it only copies the content, but not the formation?

View 5 Replies View Related

Specific Cell To Run A Specific Formula

Aug 14, 2009

let say in cell A6 i have a list

SnG
Cash
MTT

I can pick one of these. If i pick Cash then i want a specific cell to run a specific formula. depending on the item chosen.

So in cell A6 is the list.

in cell B6 an amount will be place and in C6 another amount will be place. the number for c6 will range from a -number to a +number.

But D6 looks to see if B6 meet the requirements for MTT, SnG or Cash depending on which item was selected from the list. and uses the formula below with $c$3 changing from to either $D$3 or $F$3 and comparing the values for the chosen item. the formula changes would look this

=IF(ISBLANK(C6),IF(B6>$C$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))

=IF(ISBLANK(C6),IF(B6>$D$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))

=IF(ISBLANK(C6),IF(B6>$F$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))

View 12 Replies View Related

Look For Character In Cell And Insert New Character - For More Than One

Sep 20, 2009

I have a cell which will contain SER01+SER02+SER03

and what i need it to contain is [SER01]+[SER02]+[SER03]

and shocker is i've got this to work for the first instance but not the other two

code as below... be grateful for your help

Sub measure1()
Dim list As String, pos As Integer, refl As String, refr As String, newlist As String
list = Cells(1472, 16).Value
pos = InStr(list, "+")
refl = Left(list, pos - 1)
refr = Right(list, pos + 1)
newlist = "[" & refl & "]"
Cells(1472, 17) = newlist
End Sub

View 9 Replies View Related

OR Function: Multiply The Cell

Oct 24, 2009

I have a row where C5 is the total of C1:C4. What I am trying to do is if C1 or C2 or C3 or C4 has a number value that I can multiply the cell in question by 2 and then add up all of the cells to give a total in C5. Also, I have a name in A1, but when I select E1 and enter =A1 into the cell then return, I get A1 in the cell instead of the name in A1.

View 5 Replies View Related

VBA Multiply Only If Cell Is Not Blank?

Jul 14, 2014

I need a VBA macro to multiply column E and F and deposit the result in column G. The multiplication should only happen when there is a value in the column E. If there is no value in the column E, then the cell in the G column should stay blank.

View 9 Replies View Related

VBA Loop - Remove Single Character From Cell String Then Calculate New Value In Cell

Mar 1, 2012

I have a string of text in cell A2. In cell B2 of my spreadsheet is a formula that calculates a number based on the text string in cell A2.

I want to write a VBA loop that removes a single character from the cell A2 string, then calculate the new value in cell B2. I want this loop to continue until the value in B2 falls below a set value (in this case 60).

My code so far
Sub trim_text()
Dim mytext As String
Dim myanswer As Integer
mytext = Range("A2")
myanswer = Range("B2")
Do While myanswer > 60
mytext = (Right(mytext, Len(mytext) - 1))
Loop
End Sub

This obviously does not work. In my excel table I have a formula in cell B2 to calculate "myanswer" will this work, or does that code have to be placed into the VBA code?

View 3 Replies View Related

Minus Number From Cell And Result In Specific Cell And Specific Column

Jan 20, 2009

I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)

second question

I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)

View 3 Replies View Related

Multiply The Cell Value Inside A With Range

Feb 21, 2008

After formating I need to multiply by 2 . As you can see my Cells.Value = Selection.Value * 2 is not correct. How can I accomplish this?

With Range("I17:I4000", Range("I" & Rows.Count).End(xlUp)).Offset(, 1)
.Formula = "=ReplaceChar(I17)"
.Value = .Value
Selection.NumberFormat = "0.00"
Cells.Value = Selection.Value * 2
End With

View 9 Replies View Related

Put Manually Formula In Specific Cell?

Jun 5, 2014

I would like to be able to put manually a formula in a specific cell (like a "requested date" cell) and then have a table that use this date for the headers and calculate +1 year for each columns.

For exemple:

=2012-01-01
=2013-01-01
=2014-01-01
=2015-01-01

requested date:
2012-01-01

Would you know a way to easily do it? when I try to do it manually (+365) it doesn't consider leap years. I thought about a =DATE formula but the requested date have to be manually entered...

View 3 Replies View Related

Using Only Specific Cell Either For Entry Or Formula

Apr 25, 2007

I have a problem to solve

I have 2 worksheets named 'Products' and 'Order'

At the 'Order' i use 3 cells

Code
Pallets
Cases

When i entered a code to the cell 'code', it reads the code from the sheet 'Products' and then transfers to the active sheet 'Order' some items.

What i want is:
Because the user has to enter the quantity either to the cell 'Pallets' or
'Cases' i want to be locked the empty cell each time and to be used for calculations.

1st Eg.
The "45870"
Pallets to order : 50
because the user used the pallets the cell "Cases" must be locked and it must calculate the 50 pallets*100 so
Cases : 5000

2nd Eg.
The "45870"
Pallets to order : null
Cases : 5000

because the user didnt use any pallets the cell "Pallets" must be locked and it must calculate the 5000 cases/100 so
Pallets to order : 50

View 9 Replies View Related

Formula: Higher, Lower Or The Same To A Specific Cell Value

Aug 24, 2009

I have a spreadsheet that I am using to track competitor retail pricing
The problem is that I have a list of values and I want to be able to show how many of these values are either higher, lower or the same to a specific cell value. However one complexity that I have is that some of the competitors do not have values for all records (i.e. some are blank) and I want to discount these from the counting.

View 12 Replies View Related

Formula To Reference A Specific Variable Cell

Jan 13, 2009

Not been around for some time now but have to do my annual update for this workbook and implement some new changes.

In the attached example workbook, I have created some formulas which reference a particular cell for each client e.g.

View 2 Replies View Related

Formula To Extract Specific Data In Cell?

Dec 2, 2011

I have data in several cells, and I want to extract only the information in the cell up to a /. For example "joesmith/shipping" I would like to have only joesmith in the cell next to it.

View 2 Replies View Related

Multiply By Info In Different Columns - Selected By Input In Cell

Mar 19, 2014

I'm trying to make a sheet to calculate my taxes. I would like to keep tax rates from previous year(s), and have them applied based on which year I input in a cell..

Let say that in cell B1 I write year 2013

In cell G1 I have year 2013 and in cells G2:G7 I have tax rates from year 2013.
In cell H1 I have year 2014 and in cells H2:H7 tax rates from year 2014

In cell B17 to B20 I would like to make a formula that multiplies cell B11 or B12 with information found in either column G or H based on input in B1.

I have attached a spread sheet as example.

I know I can make a formula based on IF(B1=2013;G*;IF(B2=2014;H*;etc..

View 3 Replies View Related

Single Cell Two Numerical Values - Multiply In Different Columns

Jun 27, 2014

I am looking for formula , single cell two numerical values , multiply in different columns

example :

Cell (A1) contain: =350,23+25,59 (two values in a single cell)

request:

in (B2) only first value so 350,23 multiply (x) by fixed number &in (C2) only second value so 25,59 multiply (x) by fixed number

View 2 Replies View Related

If A1,A2orA3 Is Greater Than 0 Then Multiply By 2 Or Leave The Cell Blank

Jan 14, 2010

=IF(OR(A1>0,A2>0,A3>0),*2,"")

if A1,A2orA3 is greater than 0 then multpy by 2 or leave the cell blank

View 9 Replies View Related

Multiple Cell Formatting (number Less Than 10 Multiply By 1000)

Feb 11, 2010

How can i format a column of cells so that if a number is less than 10 for eg that that particular cell is then multiplied by 1000.

View 9 Replies View Related

Formula To Search A Cell For Specific Data To Extract

Mar 13, 2009

I was wondering if there is a formula that will search a cell for a word or other specific criteria then if it finds it, puts the requested data / word in the formula cell

Example:

If I have a list of vehicle descriptions all in different formats:

CAR1 1.4 SRI 3 door Hatch
CAR2 5 door saloon GSI 2.0 V8
2.2 CDX 5 door CAR3 Estate
CAR3 Estate 5 Door CDX

Say I want to know which ones are CDX varient I need the formula to look in the cell and return "CDX" or "YES"

View 4 Replies View Related







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