Dividing Data In A Cell If Conditions Met In Another Cell

Jan 4, 2009

I'm a Graphic Designer and am just starting to learn how much fun Excel can be. I'm still learning things though and this is an easy one I think, but I can not figure it out.

All I want to do is the following...

If there is any data present at all in column B on a particular row, then I want the number on the same row in Column D to be divided by 2.

In the example below... TB_5129-001 is present in B8. So I would want $45.00 (D8) to be divided by two automatically. Also, if B8 were to be blank i would want D8 to be left alone.

View 7 Replies


ADVERTISEMENT

Dividing Hard-coded Data By Named Fixed Cell?

Feb 6, 2014

I am trying to divide hard coded financial data by a named cell that will allow me to toggle viewing the financial data in dollars, thousands of dollars, and millions of dollars. I have named my toggle cell _000s and I have used Go To Special to highlight all of the hard coded data in my financial spreadsheet. Once I have done this I would like to Copy my _000s toggle and divide the hard coded values by this cell. I am using copy and then paste special divide by but I am having no luck with this tactic.

faster more efficient way to go about this rather than individually changing each hard coded item?

View 3 Replies View Related

Dividing Cell By Another Cell Of Same Row But Different Column

Jan 18, 2014

I need to divide Cell A1 by another cell in same row but different column. The column number will be based on a reference cell. If ref cell =6 then formula would be A1/a6. Seems simple and more than one way to do it via cell formula. I will be copying and filling this formula down many rows, but cell reference will remain fixed for all.

View 1 Replies View Related

Dividing Multiple Arguments In Same Cell

Jul 26, 2014

V = 100 x [E /(IY + YP)] x Sqr. [((R + G)/2 / (IY + YP + F)]
V = 100 x [6.10 /(5.60)] x Sqr. [((10.2+2)/2 / (5.60 + 2.1]
V = 108.93 x Sqr (.79)
V = $96.89 / share as of 7/18/2014

E = Earnings in $ / share 6.1 (b9)
IY = Interest rate in % 5.6 (b10)
R = Profitability Factor in % (internal growth rate)10.2 (b11)
G = Earnings Growth in % / yr. 2 (b12)
F = Inflation Rate in % / yr. 2.1 (b13)

Here's what I have, result should be $96.89, I need to tell excel to take square root of everything to the right not just (B11+ B12)

=100*(+B9/B10)*SQRT(B11+B12)/2/(B10+B13)

View 3 Replies View Related

Diagonally Dividing Individual Cell

Nov 5, 2008

how could divide one single cell in diagonal direction ?? without using line from drawing tool.

View 12 Replies View Related

Dividing Range Of Cells With Given Cell Having Formula?

Sep 3, 2012

I am working an Excell sheet. It have many cells with formula like sum of a range of cells etc.

I wanted to divide a range of cells with the given cell (which is inturn having a formula for sum of a range of cells.) want i wanted is to divide a range of cell values with a given cell whose value (number) is obtained through a formula.

when i do

=100*(v65)/v20 for T65 cell and copy the formula for T66-T106 cell range

it is calculating for T66=100*(v66)/v21 and T67=100*(v67)/v22 ........T106=100*(v106)/v61

what I want is all the cells T66=100*(v66)/v20 ... T106=100*(v106)/v20.

View 1 Replies View Related

Dividing Number Of Steps In One Cell Into Multiple Cells?

Aug 15, 2014

I have the onerous task of dividing up a series of steps in one cell into multiple cells. I tried the text to columns utility, but it only delimits once, not multiple times. Below is a data example:

"1) Load App
2) Login using Administrator Access details .
3) Click on icon 'Admin' on the right hand side of page.
4) click on subcategory 'Administration'"

"1) Load App
2) Login using Administrator Access details .
3) Click on icon 'Admin' on the right hand side of page.
4) Click on subcategory 'Administration' .
5) Click on any Organization name from 'Organization' section in the list.
Or click on 'Add Organization' icon in section 'Organization' .
6) Validate field 'Parent Organization' in 'New Organization' or 'Edit Organization' form"
"1) Load Documoto.
2) Login using Administrator Access details .
3) Click on icon 'Admin' on the right hand side of page.
4) Click on subcategory 'Administration' .
5) Click on any Organization name from 'Organization' section in the list.
Or click on 'Add Organization' icon in section 'Organization' .
6) Validate field 'Tenant ERP Accessible' in 'New Organization' or 'Edit Organization' form"

1) Load App
2) Login using Administrator Access details .
3) Click on icon 'Admin' on the right hand side of page.
4) Click on subcategory 'Administration' .
5) Click on any Organization name from 'Organization' section in the list.
Or click on 'Add Organization' icon in section 'Organization' .
6) Validate field 'DMZ license key expiration period ' by click on checkbox or by 'Uncheck' checkbox"

I need to put each step into it's own cell.

View 1 Replies View Related

Simple Macro For Dividing Cell Content By 100 Needed

Dec 14, 2006

I'm only starting to get into the Macro side of excel, and I've created a couple of macros to automatically format cell contents and the like.
However I'm having trouble trying to divide some cell contents by 100.

I have 2 files I'm working with, one contains information regarding cd's and percentages, however the percentages in this file cannot be formatted to percentages (so the powers that be say) for whatever reason.
I copy all this information into another file which does have the percentages formatted as percentages, the result is that the values get multiplied by 100.

So values that read:
100
50
50.25

for example, appear on the new sheet as:
10000%
50000%
5025%

Is it possible to write a macro that will divide these percentages by 100 so the read correctly as:
100%
50%
50.25%

the macro will have to work on selected ranges.

View 9 Replies View Related

Excel 2010 :: Dividing Numbers By Number Of Cell They Are Found In?

Feb 13, 2012

I am using excel 2007 or 2010.

I need to divide the numbers on sheet 1 column D. This column contains numbers and blank cells so I need to divide by the actual number of cells that contain only numbers. I will be referencing the divided numbers on sheet 2 in a monthly summary format.

View 3 Replies View Related

Dividing Output Of Sumproduct Formula By Count Of Specific Cell Contents

Aug 21, 2009

I've got the following sumproduct formula (which seems to work):

=SUMPRODUCT(--(ISNUMBER(SEARCH($B17,Product_Keywords))),--(YEAR(Invoice_Date)=H$3),--(ROUNDUP(MONTH(Invoice_Date)/3,0)=H$4),--(Invoiced_Amount))

B17 is a keyword to be found in the Product_Keywords range

I'd like to modify it so that:

ifthe cell in the range Product_Keywords also includes "," thendivide the sumproduct by the number of commas+1

Presumably I could add --(ISNUMBER(SEARCH(",",Product_Keywords) as one of the conditions,
but how would I keep track of the number of commas in the cell?

View 9 Replies View Related

If No Data, Then Show Empty Cell Using Conditions

Oct 22, 2009

I'm using Excel 2002 and am having trouble with what I thought was a simple conditional command. On part of the spreadsheet I have 3 columns: Hours (D5), Cost Per Hour (E5) and Total Cost (F5). Here's what I'm trying to do.....

If there is no value in the Hours then the Total Cost should show an empty cell and conversly if there is a value in Hours then calculate Hours*Cost Per Hour in the Total Cost cell. My condition for cell F5 is as follows: =IF(D5="","",SUM(D5*E5)). Whilst it works if there is a value in D5, when empty F5 shows #VALUE!

View 5 Replies View Related

Fill Range With Data Meeting Conditions Based On Cell Value

Apr 23, 2009

refer to attached worksheet.

I need a way (Non-Macro please) - where if a condition is met, then fill a range with a particular value. The attached spreadsheet has a sample with explanation.

View 9 Replies View Related

Populate A Cell Based On Conditions Of Other Cells W/out Putting A Formula In The Cell

Oct 10, 2009

Is there a way to make a cell populate certain text based on conditions of other cells without putting the formula in the cell you want to populate. So that someone could type other text into the cell if the conditions were not met?

View 14 Replies View Related

Colour Cell Based On Few Conditions / Cell Values Of Other Cells In Same Row?

Jun 28, 2014

In the xls for each step I have 2 raws-planned and actual. Step planned duration is populated manually over the weeks.Before that row we have another reflecting the actual step status per week

I would like to find a way how cell reflecting the actual status of a step can be automatically populated (coulored) based on the colour/value of activities that are planned for that week and for that step.Activities are listed below the step and again have planned and actual row.

The rule should be : if for a week we have several activities all of them should be finished in order to have step stataus auto populated as green. If a single activity planned for that week is not done-then weekly step status should be red.

The activities for each step are grouped below the step. It seems that one of the difficult part in that request would be how formula will understand where starts and finishes the activities that belong to one step. To get that happen I placed a column showing step and another column where we activity.

View 9 Replies View Related

Changing Colors In Specific Cell When Conditions Met In Another Cell

Apr 22, 2014

I need to change the color of a specific cell when conditions are met in a different cell and it seems like it may be beyond the scope of simply applying conditional formatting. What vba code would look like in proper syntax to make the following statement true. IF cell value Q4 is >=cell value S4 AND cell value R4 is <100 Then cell Q4 turns red and IF cell value Q4 is >= cell value S4 AND cell value R4 =100 then cell Q4 turns green. it may also be useful to know that the "cell values" in this example will be dates and not straight values. this may be possible to do using conditional formatting, but i cannot find a formula that works.

View 2 Replies View Related

Dividing Data Compiled On One Worksheet To Others

Apr 15, 2009

In trying to solve my dilema I searched some older threads and found some code written by Peter SSs. I altered it to fit my situation ( or so I thought) because all I get is a compile error. I am not sure why. The data is lengnty but the sorting item is the provider name. The information for each provider should then post to the sheet with his name.

Here is the
Sub test()
Dim lr As Long
Dim ws As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False
lr = Sheets("Audit Results").Range("F" & Rows.Count).End(xlUp).Row
For Each ws In ThisWorkbook.Worksheets

View 11 Replies View Related

Count Cell Value If Conditions Met

Jan 22, 2009

I have a worksheet with 3 columns in it. these are entitled "area", "uploaded" and "status". uploaded will be a numerical value and status will either be "awaiting signoff" or "completed" what i need to do is list all of the different areas and add the "uploaded" values together IF the status is completed.

View 4 Replies View Related

3 IF Conditions But Formula In One Cell

Feb 18, 2013

"IF" formula. I want this in one cell but not able to do so :

If A1180, then Over Utilized, If A1=180, then Fully Utilized.

If the formula returns 0,1,2 instead of Under Utilized,Over Utilized, Fully Utilized respectively, I have no problems.

View 1 Replies View Related

Count A Cell Only If It Met One Of Three Conditions

Apr 13, 2007

What formula would I use to count a cell only if it met one of three conditions?

ie only if the cell equalled 1, 2 or 3 but not 4?

View 9 Replies View Related

Check Cell For 2 Conditions

Sep 30, 2007

I have a sheet here whereby if Bor No = 1, then total incurred should return as incurred. But if BOR No is more than 1, then the total incurred should sum up seq 1 and 2 of incurred. Bear in mind that seq no could be up to 4, 5, 6.. and it's not fix. But if BOR No = 0 or NULL, then total incurred should equal to "0"

View 5 Replies View Related

VBA To Set Cell Value Based On Multiple Conditions

Jan 4, 2013

I am creating a document log that tracks all excel files sent and received.

I use RDBmerge to get the filenames and data from the file batches.

My current macro edits the data down to the last stage of data needed to create the log.

My example workbook shows the final stage of the RDBMerge Data "Rough_Data" and then the final data formatted needed for the Log "Final_Data"

Included on the "Rough_Data" worksheet is the Code log used to complete the "Final_Data" worksheet.

I have highlight the cell range on the "Final_Data" Sheet that I need to complete.

filenames can occur multiple times from the RDBmerge, so only one occurance of the filename is place in column B of the "Final_Data" worksheet.

The criteria is this:

A column on the "Final_Data" worksheet as been Named for Each of the possible "Record Types" from Column G of the "Rough_Data" Worksheet. ("A,B,C,D,E,I,O,P,Q,T,V,W,X,Y")

I then must look through the "Rough_Data" worksheet for the first occurrence (if any) of that record type for each filename on the "Final_Data worksheet. If an occurrence is found I then must look at the "Unique ID" & "Program" Columns of the "Rough_Data" Worksheet. The key (on the "Rough_Data" worksheet) is used to determine the code that is written to the corresponding cell.

i.e.
"Final_Data"Date
FileName
Field2
User
Direction
Method

[Code] .....

I would very much like to automate this process, as sometimes I am dealing with over a hundred files with 30 plus rows of data each.

View 9 Replies View Related

Changing Cell Colour On Certain Conditions

Sep 15, 2009

I am an Excel newbie and would like your help to develop the attached spread sheet. I would like the colour of cells A4 to H4 to change colour when the date in column B is plus 10 days and the same to happen to A5 to H5 so on and so on

I would also like the cells A4 to P4 to change colour when a quote goes to become an order an we enter data into J4 to P4 again the same thing to happen in subsequent lines as data is entered in the relevant line.

Basically I want a quote of 10 days or more to show as a different colour so that an employee can chase the quote. Once the quote become a firm order I want the cells to become a different colour again.

View 13 Replies View Related

VB Color Formatting More Than 4 Cell Conditions

Jun 25, 2009

The code below turns numbers and dates on my worksheets(1) to text priorities on my worksheets(3), I now need to color the cell backgrounds of the results on worksheets(3). With formulas I know I can conditional format up to 3 colors (or 4 utilising the background) but I am after 6. I also know that you will look at my code and laugh as I should have done the color coding with the initial commands, but I struggled with it for 2 days and simply couldn't get it to work.

In the VB below it all works off a command button and up to the third color it works however it then gets stuck and throws up a runtime error '1004' Application defined or object defined error. I did get around this before by recording macros and effectively sticking them together but it hasn't worked on this occasion...... any ideas??? (I hope I'm not breaking any rules by pasting the code directly in?)

View 6 Replies View Related

How Do I Highlight A Cell Based On Conditions Of Another?

Aug 5, 2009

I want to highlight A1 red if C1 is greater than 55. Is this possible?

View 3 Replies View Related

Cell Value Based On Multiple Conditions

Feb 23, 2010

I need to create a macro that will calculate a value based on the contents of multiple cells. Looking at the example attached, if columns A and/or B (employee ID and name) are empty, then allowable OT (G) should be 0. If either have data, then if Stage (F) is CAN, G should be 20. If Stage is FAS or FAR, G should be 10.

I'm not very savvy with IF statements, which is how I imagine this can be done, and I don't know if this would be easier to do as a macro or as a formula within G. Since the contents of the of the cells will be changing on a weekly basis, I'd prefer the formula to only be there if there is content on the line, so we don't have nulls showing.

View 7 Replies View Related

Show Value For Match W/2 Cell Conditions

Feb 17, 2009

In my example booklet. I've got two sheets.

Sheet 1 has 9 inputs (rows) from 5 sources: A,B,C,D,E (columns)
Sheet 1 also has flag columns to match inputs based upon similiar values (names) in description columns.

Sheet 2 - has two tables,
first table matches values of different sources w/ similiar descriptions
onto the same row.

table two: displays, min. value for each row in first table ...

View 14 Replies View Related

Programming Multiple Conditions In Cell

Jul 6, 2009

Attached is my worksheet. I need to program column C to adjust the value in column A according to the rules set in table J2:K17, but ONLY if the value in column B = mens. If the value in B does not equal mens, then leave the C cell blank. I've manually populated column C to show the end result that I'm looking for.

I tried a simple =IF(ISNUMBER(SEARCH("Small",A2)),"S",""), etc., but I can't figure out how to incorporate the additional condition that would let the cell return a value ONLY when a condition in B is also met.

NBVC helped me with a similar request previously, but with that formula, if the condition wasn't met, the cell returned "#N/A" instead of being blank.

View 14 Replies View Related

Match A Cell Record To Several Conditions

Dec 3, 2006

How do I express in a formula if ce406= any one of the following results, then Yes, if not then No:

1 2 1 1 1 2
1 2 1 1 2 1
1 2 1 2 2 2
1 2 2 1 1 2
1 2 2 1 2 1
1 2 2 2 2 2
2 2 2 1 1 2
2 2 2 1 2 1
2 2 2 2 2 2

I tried the following formula, however, it did not give the desired result: ...

View 9 Replies View Related

Reset Cell Value Based On Two Conditions

Mar 10, 2009

I am trying to build a spreadsheet that tallies hours worked and hours available for the next day. The key to this spreadsheet is the number 70. A worker can accumulate no more then 70 hours in an 8 day period, however after any day with no hours worked the rule resets to 70 hours available again. I have attached a sample spreadsheet so you can see how this should work.

View 9 Replies View Related

Highlight Cell Based On 2 OR Conditions

May 15, 2008

I am seeking assistance with code for column H to be highlighted when I have a value of "Pending" or "Work In Progress" is present in column E?

View 3 Replies View Related







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