How do you use a If/then statement?

  • Thread starter Thread starter JBG
  • Start date Start date
J

JBG

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.
 
Not sure I get the question
If I do, then IF is not really needed
=(D1>4%)*33% + (H1>0)*33% + (L1>0)*33%
If I misunderstood, please give more detail
D is not a row but a column.
 
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
 
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
 
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
 
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%.
 
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((D1>0.04)/3+(H1>0)/3+(L1>0)/3,2)

and copied it into P2:P4, and it gave the results you expect.

What more can I add?

Hope this helps.

Pete
 
Sorry, slight amendment needed:

=ROUNDDOWN((D1>=0.04)/3+(H1>0)/3+(L1>0)/3,2)

Hope this helps.

Pete
 
Back
Top