Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do you use a If/then statement?
I have row D that needs to be over 4.00% to be counted 33%
Row H that needs to be a positive number to be counted 33% Row L that needs to be a positive number to be counted 33% and the sum of all three rows add in row P. The range could be 0%, 33%, 66%, or 100% I hope this makes since. I also did not want to create any rows that contained any information other than row P. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do you use a If/then statement?
Not sure I get the question
If I do, then IF is not really needed =(D14%)*33% + (H10)*33% + (L10)*33% If I misunderstood, please give more detail D is not a row but a column. -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "JBG" wrote in message ... I have row D that needs to be over 4.00% to be counted 33% Row H that needs to be a positive number to be counted 33% Row L that needs to be a positive number to be counted 33% and the sum of all three rows add in row P. The range could be 0%, 33%, 66%, or 100% I hope this makes since. I also did not want to create any rows that contained any information other than row P. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do you use a If/then statement?
Sorry about the confusion about rows and columns. When I tried the formula
it resulted to 0. When I look at the data it should be 100%. Let me try it again. Column D is 1/3 of the total percentage of column P. The same with column H and L. So if Column D is larger than 4 and column H and L both are negative numbers then P should be 33%. The same if D and L were not met and H was the same 33%. If D and L both met their requirements it shold be 66%. The columns can be interchanged, but I hope that you are getting what I am trying to do with this worksheet. This is what makes since to me if all three columns are met then P should be 100% if one of the columns do not met their criteria then P should be 66% if one column is met it is 33% and if no columns are met then P should be 0%. Does this help? Joshua "Bernard Liengme" wrote: Not sure I get the question If I do, then IF is not really needed =(D14%)*33% + (H10)*33% + (L10)*33% If I misunderstood, please give more detail D is not a row but a column. -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "JBG" wrote in message ... I have row D that needs to be over 4.00% to be counted 33% Row H that needs to be a positive number to be counted 33% Row L that needs to be a positive number to be counted 33% and the sum of all three rows add in row P. The range could be 0%, 33%, 66%, or 100% I hope this makes since. I also did not want to create any rows that contained any information other than row P. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do you use a If/then statement?
You are talking about columns. You say column D is 1/3 of the percentage of
column P. You mean all of column D's cells, 65,536 in pre-Excel 2007 and 1,048,576 in Excel 2007 are 1/3 of column P's cells of 65,536 in pre-Excel 2007 and 1,048,576 in Excel 2007? Tyro "JBG" wrote in message ... Sorry about the confusion about rows and columns. When I tried the formula it resulted to 0. When I look at the data it should be 100%. Let me try it again. Column D is 1/3 of the total percentage of column P. The same with column H and L. So if Column D is larger than 4 and column H and L both are negative numbers then P should be 33%. The same if D and L were not met and H was the same 33%. If D and L both met their requirements it shold be 66%. The columns can be interchanged, but I hope that you are getting what I am trying to do with this worksheet. This is what makes since to me if all three columns are met then P should be 100% if one of the columns do not met their criteria then P should be 66% if one column is met it is 33% and if no columns are met then P should be 0%. Does this help? Joshua "Bernard Liengme" wrote: Not sure I get the question If I do, then IF is not really needed =(D14%)*33% + (H10)*33% + (L10)*33% If I misunderstood, please give more detail D is not a row but a column. -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "JBG" wrote in message ... I have row D that needs to be over 4.00% to be counted 33% Row H that needs to be a positive number to be counted 33% Row L that needs to be a positive number to be counted 33% and the sum of all three rows add in row P. The range could be 0%, 33%, 66%, or 100% I hope this makes since. I also did not want to create any rows that contained any information other than row P. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do you use a If/then statement?
See reply to your other post, as well as Bernard's above - please do
not multi-post. Check your data. Are your values true numbers, or could they be text that just looks like numbers? Pete On Feb 29, 3:31*am, JBG wrote: Sorry about the confusion about rows and columns. *When I tried the formula it resulted to 0. *When I look at the data it should be 100%. *Let me try it again. Column D is 1/3 of the total percentage of column P. *The same with column H and L. *So if Column D is larger than 4 and column H and L both are negative numbers then P should be 33%. *The same if D and L were not met and H was the same 33%. *If D and L both met their requirements it shold be 66%. * The columns can be interchanged, but I hope that you are getting what I am trying to do with this worksheet. This is what makes since to me if all three columns are met then P should be 100% if one of the columns do not met their criteria then P should be 66% if one column is met it is 33% and if no columns are met then P should be 0%. * Does this help? Joshua "Bernard Liengme" wrote: Not sure I get the question If I do, then IF is not really needed =(D14%)*33% + (H10)*33% + (L10)*33% If I misunderstood, please give more detail D is not a row but a column. -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "JBG" wrote in message ... I have row D that needs to be over 4.00% to be counted 33% Row H that needs to be a positive number to be counted 33% Row L that needs to be a positive number to be counted 33% and the sum of all three rows add in row P. *The range could be 0%, 33%, 66%, or 100% *I hope this makes since. I also did not want to create any rows that contained any information other than row P.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do you use a If/then statement?
Tried both formulas. Let me give you some data to see if this helps.
Column D Column H Column L Column P 4.92% 1.12% -.92% ? (should be 66%) 3.99% .12% -.1.24% ? (should be 33%) 4.01% 2.24% .98% ? (should be 100%) 3.92% -.25% -1.75% ? (should be 0%) The question mark is how it looks now I am trying to find a formula that would give me the results that are in quotations. Once again the cut off for column D is greater than or equal to 4.00% for it to be 33% H needs to be a positive number to be counted 33% and L needs to be a positive number to be counted 33%. Then if all three conditions are met then the cell in column P should be 100% if only one condition of three is met =33%, two conditions =66% and no condions =0%. I hope that with the data it will be easier for you to understand what I am needing. The two formulas that were given to me resulted in 0 when it should be 100%. "Pete_UK" wrote: See reply to your other post, as well as Bernard's above - please do not multi-post. Check your data. Are your values true numbers, or could they be text that just looks like numbers? Pete On Feb 29, 3:31 am, JBG wrote: Sorry about the confusion about rows and columns. When I tried the formula it resulted to 0. When I look at the data it should be 100%. Let me try it again. Column D is 1/3 of the total percentage of column P. The same with column H and L. So if Column D is larger than 4 and column H and L both are negative numbers then P should be 33%. The same if D and L were not met and H was the same 33%. If D and L both met their requirements it shold be 66%. The columns can be interchanged, but I hope that you are getting what I am trying to do with this worksheet. This is what makes since to me if all three columns are met then P should be 100% if one of the columns do not met their criteria then P should be 66% if one column is met it is 33% and if no columns are met then P should be 0%. Does this help? Joshua "Bernard Liengme" wrote: Not sure I get the question If I do, then IF is not really needed =(D14%)*33% + (H10)*33% + (L10)*33% If I misunderstood, please give more detail D is not a row but a column. -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "JBG" wrote in message ... I have row D that needs to be over 4.00% to be counted 33% Row H that needs to be a positive number to be counted 33% Row L that needs to be a positive number to be counted 33% and the sum of all three rows add in row P. The range could be 0%, 33%, 66%, or 100% I hope this makes since. I also did not want to create any rows that contained any information other than row P.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do you use a If/then statement?
Well, perhaps your data is not on the same row as the formula refers to.
I added your data to a new sheet in rows 1 to 4 in the columns stated, and put this formula (slightly amended to give you 66% rather than 67%) in P1: =ROUNDDOWN((D10.04)/3+(H10)/3+(L10)/3,2) and copied it into P2:P4, and it gave the results you expect. What more can I add? Hope this helps. Pete "JBG" wrote in message ... Tried both formulas. Let me give you some data to see if this helps. Column D Column H Column L Column P 4.92% 1.12% -.92% ? (should be 66%) 3.99% .12% -.1.24% ? (should be 33%) 4.01% 2.24% .98% ? (should be 100%) 3.92% -.25% -1.75% ? (should be 0%) The question mark is how it looks now I am trying to find a formula that would give me the results that are in quotations. Once again the cut off for column D is greater than or equal to 4.00% for it to be 33% H needs to be a positive number to be counted 33% and L needs to be a positive number to be counted 33%. Then if all three conditions are met then the cell in column P should be 100% if only one condition of three is met =33%, two conditions =66% and no condions =0%. I hope that with the data it will be easier for you to understand what I am needing. The two formulas that were given to me resulted in 0 when it should be 100%. "Pete_UK" wrote: See reply to your other post, as well as Bernard's above - please do not multi-post. Check your data. Are your values true numbers, or could they be text that just looks like numbers? Pete On Feb 29, 3:31 am, JBG wrote: Sorry about the confusion about rows and columns. When I tried the formula it resulted to 0. When I look at the data it should be 100%. Let me try it again. Column D is 1/3 of the total percentage of column P. The same with column H and L. So if Column D is larger than 4 and column H and L both are negative numbers then P should be 33%. The same if D and L were not met and H was the same 33%. If D and L both met their requirements it shold be 66%. The columns can be interchanged, but I hope that you are getting what I am trying to do with this worksheet. This is what makes since to me if all three columns are met then P should be 100% if one of the columns do not met their criteria then P should be 66% if one column is met it is 33% and if no columns are met then P should be 0%. Does this help? Joshua "Bernard Liengme" wrote: Not sure I get the question If I do, then IF is not really needed =(D14%)*33% + (H10)*33% + (L10)*33% If I misunderstood, please give more detail D is not a row but a column. -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "JBG" wrote in message ... I have row D that needs to be over 4.00% to be counted 33% Row H that needs to be a positive number to be counted 33% Row L that needs to be a positive number to be counted 33% and the sum of all three rows add in row P. The range could be 0%, 33%, 66%, or 100% I hope this makes since. I also did not want to create any rows that contained any information other than row P.- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do you use a If/then statement?
Sorry, slight amendment needed:
=ROUNDDOWN((D1=0.04)/3+(H10)/3+(L10)/3,2) Hope this helps. Pete On Feb 29, 3:13*pm, "Pete_UK" wrote: Well, perhaps your data is not on the same row as the formula refers to. I added your data to a new sheet in rows 1 to 4 in the columns stated, and put this formula (slightly amended to give you 66% rather than 67%) in P1: =ROUNDDOWN((D10.04)/3+(H10)/3+(L10)/3,2) and copied it into P2:P4, and it gave the results you expect. What more can I add? Hope this helps. Pete "JBG" wrote in message ... Tried both formulas. Let me give you some data to see if this helps. Column D * * * * * * * * Column H * * * * * * * *Column L Column P 4.92% * * * * * * * * * * * * * *1.12% * * * * * * * * * * -.92% * * * * ? (should be 66%) 3.99% * * * * * * * * * * * * * * .12% * * * * * * * * * * *-.1.24% * * * * ? (should be 33%) 4.01% * * * * * * * * * * * * * *2.24% * * * * * * * * * * *.98% * * * *? (should be 100%) 3.92% * * * * * * * * * * * * * * -.25% * * * * * * * * * * *-1.75% * * * *? (should be 0%) The question mark is how it looks now I am trying to find a formula that would give me the results that are in quotations. *Once again the cut off for column D is greater than or equal to 4.00% for it to be 33% H needs to be a positive number to be counted 33% and L needs to be a positive number to be counted 33%. *Then if all three conditions are met then the cell in column P should be 100% if only one condition of three is met =33%, two conditions =66% and no condions =0%. *I hope that with the data it will be easier for you to understand what I am needing. *The two formulas that were given to me resulted in 0 when it should be 100%. "Pete_UK" wrote: See reply to your other post, as well as Bernard's above - please do not multi-post. Check your data. Are your values true numbers, or could they be text that just looks like numbers? Pete On Feb 29, 3:31 am, JBG wrote: Sorry about the confusion about rows and columns. *When I tried the formula it resulted to 0. *When I look at the data it should be 100%. *Let me try it again. Column D is 1/3 of the total percentage of column P. *The same with column H and L. *So if Column D is larger than 4 and column H and L both are negative numbers then P should be 33%. *The same if D and L were not met and H was the same 33%. *If D and L both met their requirements it shold be 66%. The columns can be interchanged, but I hope that you are getting what I am trying to do with this worksheet. This is what makes since to me if all three columns are met then P should be 100% if one of the columns do not met their criteria then P should be 66% if one column is met it is 33% and if no columns are met then P should be 0%. Does this help? Joshua "Bernard Liengme" wrote: Not sure I get the question If I do, then IF is not really needed =(D14%)*33% + (H10)*33% + (L10)*33% If I misunderstood, please give more detail D is not a row but a column. -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "JBG" wrote in message ... I have row D that needs to be over 4.00% to be counted 33% Row H that needs to be a positive number to be counted 33% Row L that needs to be a positive number to be counted 33% and the sum of all three rows add in row P. *The range could be 0%, 33%, 66%, or 100% *I hope this makes since. I also did not want to create any rows that contained any information other than row P.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can an If statement answer an If statement? | Excel Discussion (Misc queries) | |||
If statement | Excel Worksheet Functions | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
If statement and Isblank statement | Excel Worksheet Functions | |||
Help please, IF statement/SUMIF statement | Excel Worksheet Functions |