Excel 2010 :: Multiplying By Percentages Based On True False Values?
Apr 6, 2014
I have a checkbox where the values are reflected as True/False in a seperate column, what i simply want to do is take the value from one cell, and add 10% of the value each time a value becomes True.
For example in this value column after the check box has been filled out it might look like this:
False
True
True
True
False
False
False
The cell then needs to take the figure, lets say, 100, add 10%, then add 10% to 110, then again and so on for however many "True" statements are in the column.
I have tried with no success with various SUM/SUMIF/COUNT/COUNTIF/IF etc cant seem to get it to work, im not sure the cell refreshes correctly after the checkbox is filled out and its not registering the new "True" value as by default its all set to "False".
View 7 Replies
ADVERTISEMENT
Aug 2, 2009
I have a worksheet where I have around 300 rows, each with 7 columns. What I want to do is add a checkbox to each column. I plan on setting non-applicable checkboxes to mixed status and locking the worksheet. I will unlock applicable checkboxes and sumif or countif their value according to row-based scoring, for example, each checked checkbox represents a value of 3. I do not know VBA and have chose to use the form control checkboxes rather than ActiveX.
I believe that a formula for this would be something like: =SUMIF(B1:B3,True,"3") or =COUNTIF($B$1:$B$3,True)*3
I am wondering firstly if I have that right and secondly if there is a way to stop my checkboxes from displaying labels. Currently, if I click on one it displays True behind the active checkbox. If I uncheck it, it displays False.
View 8 Replies
View Related
Nov 15, 2006
I am trying to Sum lines of info with "True or False" and "Yes and No". I would like to assign 1 to True and Yes and 0 to False and No when I total the rows. Never tried this in Excel, on Lotus and the formula does not work. I can find and replace, but I would like to be able to use a formula.
View 2 Replies
View Related
Feb 27, 2014
I need to count values of numbers in a row next to text(True ot False) Like:
A1____A2
True | 150
True | 66
False| 40
True | 78
False | 56
Output:
True: 294
False: 96
View 3 Replies
View Related
Sep 25, 2009
I have number data in columns F through AK. In column AL I want a formula that will look in columns F through AK from the left to right. Once it identifies a positive value I want the formula to identify if after that positive number there are any occurances where there are 6 0's in a row (anywhere up until column AK). The formula can return a True or False. I want to drag the formula down across many rows of data. If the entire row contains 0's, I do not want it identified as a TRUE
View 9 Replies
View Related
Jul 20, 2006
how return a true/false based on a cell containing a string (text) vs a value?
I have a column where most cells are blank, some contain text, and others contain dates. I would like to select and manipulate only the ones that contain dates (or values).
View 9 Replies
View Related
Mar 12, 2008
I would like to create a qestionairre with checkboxes used to answer the question "Do you need____" (Check if yes). On the following workbook I would like a list of all items needed, and a list of items not needed on yet another workbook.
Is there a way to create these lists without having blanks for values that are not true? (and because Im sure there is HOW?)
View 9 Replies
View Related
Apr 21, 2014
i have a formula in a cell:
=IF(ISERROR(VLOOKUP($D11,PRMC!$O$15:$P$1048576,2,FALSE)),"",VLOOKUP($D11,PRMC!$O$15:$P$1048576,2,FALSE))&IF(ISERROR(VLOOKUP($E11,PRMC!$O$15:$P$1048576,2,FALSE)),"",VLOOKUP($E11,PRMC!$O$15:$P$1048576,2,FALSE))
The first half of the vlookup searches for data and returns a dollar amount if true The second half of the vlookup searches for another data set and returns a dollar amount if true...
Both amounts could be the same, so I cant use a formula based on numeric value
the conditional format must determine which vlookup is returning the result
in other words I'd like to conditionally format (grey fill) if the first vlookup returns data and conditionally format (blue fill) if the second vlookup returns data
Probably have to "use a formula to determine which cells to format"
View 7 Replies
View Related
Jan 15, 2014
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
View 7 Replies
View Related
Apr 7, 2014
Basically I have 1 value on 1 sheet and and multiple values on another sheet and I would like to know if it is possoible to mutliply these togther i.e
Sheet 1
100
Sheet 2
2,5,9,15,20....
I would like to calculate (100*2)+(100*5)+(100*9)+(100*15)+(100*20).....
I know if sheet 2 only had a few numbers like above which I have used as an example I could link the sheets; however in my data, sheet 2 is about 75 numbers that change.
View 3 Replies
View Related
Sep 5, 2012
I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.
Currently...
B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))
J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))
Example:
If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.
Windows 7 Ultimate / Excel 2010
View 1 Replies
View Related
Jun 4, 2012
I have a spreadsheet (Excel 2010). I want to fill categoryid in Sheet One based on values of Skill and State which are part of field in Sheet two.
Sheet One (Has Four Columns and I am looking for filling CategoryID based on Sheet Two
FirstName LastName Skill State CategoryID
John Edward Ballet California
Ed Catalino Tap London
Natasha Curtis Ballet Australia
Shen Watson Modern Kansas
Sheet Two
CategoryID CategoryDescription
1 Dancers/Ballet/United States/Alaska
2 Dancers/Ballet/United States/California
3 Dancers/Ballet/UnitedKingdom/Wales
4 Dancers/Ballet/UnitedKingdom/London
5 Dancers/Tap/United States/Alaska
6 Dancers/Tap/United States/California
7 Dancers/Tap/United Kingdom/Wales
8 Dancers/Tap/United Kingdom/London
9 Dancers/Ballet/Australia
10 Dancers/Modern/United States/Kansas
View 5 Replies
View Related
Oct 11, 2013
I am using MS Excel 2010 want what to change a number of cells if a value is true e.g.
Row one has the headings Prz, Stat, Val, andPts
Row two has the following values :-
(Prz) a2 =2, (Stat) b2=1, (Val) c2=$30, (Pts) d2=10
How do I do the following in Excel :-
IF B2=1 THEN A2+1 AND C2+30 AND D2+5
so that the above cells are changed to
(Prz) a2 =3, (Val) c2=$30, (Pts) d2=10
View 4 Replies
View Related
May 9, 2014
I need to highlight a column in a pivot based on the 3 rules below.
Highlight cell in column A if:
Cell in Column A contains numbers 4, 5, 6. or 6.5.
Cell in Column D contains a value
Cell in Column E contains a value
An example of how it should look (Excel 2010): MrExcelhelp1.xlsx
Formula to put in conditional formatting?
View 4 Replies
View Related
Feb 26, 2014
I'm trying to use Excel to build a text file based on values from certain cells in Excel 2010. This is based partially off of static text that never changes and variables that will need to change. For example, I want Excel to output a text file that has the following text: The red fox jumped over the $X twice. I would want $X to be replaced by the value of A1 of the active worksheet. I will also have several lines like that, so it won't just be a single line, but anywhere from 20 - 120.
View 1 Replies
View Related
Jun 17, 2014
I'm new to VBA and macros, using Excel 2010, and am trying to figure out how to delete all duplicate rows in a sheet where 2 or less of their values in column A is "1". I'd like have a script that is flexible enough to change to 3 or less if need be. I also have a header row that needs to be offset in the process.
A---B-
0--123 <-delete
0--123 <-delete
0--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321
or
A---B-
0--123 <-delete
0--123 <-delete
1--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321
View 5 Replies
View Related
Mar 14, 2012
I'm trying to create a excel sheet which will automatically return a price based on a given width and drop value. Currently, I calculate the price manually by looking in a price book which has plenty of rows and columns and prices. I want to simplify this by simply entering the dimensions so it automatically calculates price based on the dimensions entered.
Below is the start of my worksheet. If I choose the exact sizes shown on the table, it will return a price, however if I choose a size that is not listed, I get an #N/A. Eg. If I choose 780 x 1500, it will return the price $179, but if I choose for instance 775 x 1490, it only returns #N/A, when I want it to still return the price $179.
My formula in K3 is
=IF(AND($I$3>0,$J$3>0),INDEX($A$3:$F$8,MATCh(J3,$A$3:$A$8,0),MATCH(I3,$A$3:$F$3,0)))
Sheet1ABCDEFGHIJK12ItemWidthHeightPrice
3mm6307809301080123019751821#N/A
49001581681791891992512001621741851952083615001661791912032167
18001701851972102228210017418920321623192400179193208224239102700
18520121823324911Excel 2010Worksheet FormulasCellFormulaK3=IF(AND($I$3>0,$J$3>0),INDEX($A$3:$F$8,MATCH(J3,$A$3:$A$8,0),MATCH(I3,$A$3:$F$3,0)))
View 4 Replies
View Related
Oct 24, 2013
This is Excel 2010 on Windows 7
Trying to figure out a VB Script but don't know where to start.
I have a sheet for each month. lets take September 2013
A Column with 200 Servers (A1:A200) on the Intranet listed as server-1/, Server-2/ Server-3 ... Server-200/
Each Server lists a location of a file for each day (31 columns per row)
I am trying to check for the existence of the files on each server (The Cells contain the URL and File Name in Range B2:AF201) starting with server-1 check the URL in B2 if it exists, go to C2, Continue with that row until the file check is False (The Page will show a 404 error) if not found delete cell url and continue. do this for all 200 rows
View 3 Replies
View Related
May 29, 2009
Is there another formula that I could use that would return YES or NO instead of TRUE or FALSE? This is the formula that I am currently using.
=COUNTIF(BG_WADDRESS,D2)>1
View 9 Replies
View Related
Nov 8, 2009
I guess the answer for this is simple but can't find the answer.
AB1FALSE IF TRUE THAT A4 = TEST12TRUE IF TRUE THAT A4 = TEST23FALSE IF TRUE THAT A4 = TEST34TEST2IF ALL ARE FALSE = ""
Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Mar 14, 2012
I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.
I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).
For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?
Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250
Payment612345678750Vacation798765432800Salary898765432250
Payment998765432100Overtime1098765432450Salary1198765432300Overtime
Sheet1
Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432
Sheet2
View 3 Replies
View Related
Nov 4, 2008
how to change the default output values "FALSE" and "TRUE" for the AND() function ? Let say I want to change them to "FAIL" "PASS".
View 2 Replies
View Related
Apr 2, 2014
In Column A I have the following values below.
Y
Yes
N
No
Questions how do you created a formula, Example If Cell A2 = Y or Yes Cell B2 will be 1, if not then 0.
Currently I was successful in writing this formula =IF(BB2="Y","1","0") but this formula only returns the value for Y, not Yes
View 3 Replies
View Related
Jan 5, 2007
I am using the following code in my Macro
Application.ScreenUpdating = False (I set it to "True" at the end of the sub)
But I can still see Excel changing tabs etc.
View 9 Replies
View Related
Jul 14, 2008
when you type True into a General formatted Excel cell Excel returns TRUE. This value cannot be formatted to a 1 (or a -1) by applying a different format. Thus, does Excel have a boolean data type? Or is it something else like a numeric value that for whatever reason is not receptive to format changes.
View 9 Replies
View Related
May 30, 2006
created my 'IF' function with the 'True/False' statements, but i am wanting to have yes/no instead and i cant figure out how to do it.
View 9 Replies
View Related
Feb 1, 2008
I am currently looking at the workings of a spreadsheet designed by someone else.
Within this sheet, a cell has the formula that , once broken down, equates to:
=100*TRUE (Cell returns a value of 100)
And another that equates to :
=100*FALSE (Cell returns a value of 0)
Am I correct in thinking that TRUE always equals 1, and FALSE always equals 0? I think this is right, but just need someone to confirm.
View 6 Replies
View Related
Apr 12, 2013
I'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:
Column F:
6
6
14
Column L:
3
NA
17
I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.
Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.
View 3 Replies
View Related
Sep 5, 2013
I've done a check box on excel (ActiveX) which then enters True or False when I tick or untick it. Is there any way which you can change the True/False words to something else?
For example, if the box was ticked it would enter "Electric" instead and "N/A" if the box was unticked?
View 2 Replies
View Related
Aug 22, 2014
On sheet 3 column d i have a vendor number. I am trying to create a formula in column M (same sheet) that will say "True" if the vendor number in column d is also listed on sheet 2, column A (Rows 2-148)
View 2 Replies
View Related
