Integrating Multiple “if” Conditions in Excel

Excel spreadsheets (.xls)

One of the recent comments that we received on our “Nested If Statements in Excel with “And” or “Not”, was an interesting example. Here is the question that we received:

I would like to know if it is possible to write a formula to determine “pass” or “fail” given the following criteria:
The scores are given as follows in excel :
example : (subjects are named A to G)
The subjects are typed in left to right in one row, not in columns.

criteria :Subject A must be >= 40, from B to G there must two other subjects >=40 and three additional >=30 – for a student to pass.

In theory, it would be possible to integrate all of that into one single formula. There are however many problems. It would be very difficult and time consuming to build and changing it would be a nightmare. Why not break down the problem down? Here is how I did it:

-Determined if the score for every subject was over or equal to 30
-Determined if the score for every subject was over or equal to 40

Once that was done, I had every information to determine the acceptance or not of each of the three conditions. It looks complex but it’s actually very easy to do and was very quick. Here is the look of my spreadsheet, which you can also download later on:

There are multiple advantages to using this more detailed method in such a case:

1-Easier to understand, we can easily see why failed students did not pass.
2-Easy to modify and adjust over time

You Can Download The SpreadSheet Here

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

15 Feedbacks on "Integrating Multiple “if” Conditions in Excel"

Rebecca

I like the information regarding nested if statements but am looking to incorporate the isblank in as well. Here is the scenario:
Column B if full of alpha numeric data. I have about 100 rows. If one cell is blank then pull the data from the cell below moving up all the rest of the rows as well. There might be multiple blanks. I was thinking of using the isblank to return a true or false and if true grab the data in the cell below moving up all the other cells as well and if false leave the data as entered. Would appreciate any comments!



Michael

I am putting together a rating system:

5.00 to 5.99 = Bronze
6.00 to 6.99 = Silver
7.00 = Gold

I was able to put this together, however I now need:

8A1 to = Diamond



Guevara Kay

=IF(A2>=50, “pass”, IF(A2=75, “dist”))). it does not want to result to “dist” if value is >= 75. please help me fix the problem



Guevara Kay

=IF(A2>=50, “pass”, IF(A2=75, “dist”))).



Guevara Kay

=IF(A2=50, “pass”, IF(A2>=75, “dist”))). it does not want to result to “dist” if value is >= 75. please help me fix the problem



Stephanie

I am trying to set up the formula below to link add up certain information from another tab in the workbook with three stipulations but it is not working.

Help!

=SUM(Valuation!Q$3:Q$11655,2), IF(AND(Valuation!B$3:B$11656=”004″,Valuation!A$3:A$11658=FG!B4, Valuation!A$3:A$11615=FG!B4))



cehj

=IF(E12=”1b”,pricelist!D3,IF(E12=”1c”,pricelist!D4,IF(E12=”1d”,pricelist!D5,IF(E12=”1e”,pricelist!D6,IF(E12=”1h”,pricelist!D9,IF(E12=”1i”,pricelist!D10,IF(E12=”1j”,pricelist!D11,IF(E12=”1k”,pricelist!D12,IF(E12=”2b”,pricelist!D16,IF(E12=”2c”,pricelist!D17,IF(E12=”2d”,pricelist!D18,IF(E12=”2e”,pricelist!D19,IF(E12=”2h”,pricelist!D22,IF(E12=”2i”,pricelist!D23,IF(E12=”2j”,pricelist!D23,IF(E12=”2k”,pricelist!D24,IF(E12=”3b”,pricelist!D29,IF(E12=”3c”,pricelist!D30,IF(E12=”3d”,pricelist!D31,IF(E12=”3e”,pricelist!D32,IF(E12=”3i”,pricelist!D33,IF(E12=”3j”,pricelist!D34,IF(E12=”3k”,pricelist!D35,IF(E12=”4b”,pricelist!D42,IF(E12=”4c”,pricelist!D43,IF(E12=”4d”,pricelist!D44,IF(E12=”4e”,pricelist!D45,IF(E12=”4h”,pricelist!D48,IF(E12=”4i”,pricelist!D49,IF(E12=”4j”,pricelist!D50,IF(E12=”4k”,pricelist!D51,IF(E12=”5b”,pricelist!D55,IF(E12=”5c”,pricelist!D56,IF(E12=”5d”,pricelist!D57,IF(E12=”5e”,pricelist!D58,IF(E12=”5h”,pricelist!D61,IF(E12=”5i”,pricelist!D62,IF(E12=”5j”,pricelist!D63,IF(E12=”5k”,pricelist!D64,IF(E12=”6b”,prcelist!D68,IF(E12=”6c”,pricelist!D69,IF(E12=”6d”,pricelist!D70,IF(E12=”6e”,pricelist!D71,IF(E12=”6h”,pricelist!D74,IF(E12=”6i”,pricelist!D75,IF(E12=”6j”,pricelist!D76,IF(E12=”6k”,pricelist!D77,IF(E12=”7b”,pricelist!D81,IF(E12=”7c”,pricelist!D82,IF(E12=”7d”,pricelist!D83,IF(E12=”7e”,pricelist!D84,IF(E12=”7h”,pricelist!D87,IF(E12=”7i”,pricelist!D88,IF(E12=”7j”,pricelist!D89,IF(E12=”7k”,pricelist!D90,IF(E12=”8b”,pricelist!D94,IF(E12=”8c”,pricelist!D95,IF(E12=”8b”,pricelist!D96,IF(E12=”8c”,pricelist!D97,IF(E12=”8d”,pricelist!D100,IF(E12=”8e”,pricelist!D101,IF(E12=”8h”,pricelist!D102,IF(E12=”8i”,pricelist!D103,IF(E12=”8j”,pricelist!D107,IF(E12=”8k”,pricelist!D108,IF(E12=”9b”,pricelist!D109,IF(E12=”9c”,pricelist!D110,IF(E12=”9d”,pricelist!D113,IF(E12=”9e”,pricelist!D114,IF(E12=”9h”,pricelist!D115,IF(E12=”9i”,pricelist!D116,IF(E12=”9j”,pricelist!D120,IF(E12=”9k”,pricelist!D121,IF(E12=”10b”,pricelist!D122,IF(E12=”10c”,pricelist!D123,IF(E12=”10d”,pricelist!D124,IF(E12=”10e”,pricelist!D126,IF(E12=”10i”,pricelist!D127,I(E12=”10j”,pricelist!D128,IF(E12=”10k”,pricelist!D129,IF(E12=”11b”,pricelist!D133,IF(E12=”11c”,pricelist!D134,IF(E12=”11d”,pricelist!D135,IF(E12=”11e”,pricelist!D136,IF(E12=”11h”,pricelist!D139,IF(E12=”11i”,pricelist!D140,IF(E12=”11j”,pricelist!D141,IF(E12=”11k”,pricelist!D142,IF(E12=”12b”,pricelist!D146,IF(E12=”12c”,pricelist!D147,IF(E12=”12d”,pricelist!D148,IF(E12=”12e”,pricelist!D149,IF(E12=”12h”,pricelist!D152,IF(E12=”12i”,pricelist!D153,IF(E12=”12j”,pricelist!D154,IF(E12=”12k”,pricelist!D155,IF(E12=”14b”,pricelist!D159,IF(E12=”14c”,pricelist!D160,IF(E12=”14d”,pricelist!D161,IF(E12=”14e”,pricelist!D162,IF(E12=”14h”,pricelist!D165,IF(E12=”14i”,pricelist!D166,IF(E12=”14j”,pricelist!D167,IF(E12=”14k”,pricelist!D168,IF(E12=”15b”,pricelist!D172,IF(E12=”15c”,pricelist!D173,IF(E12=”15d”,pricelist!D174,IF(E12=”15e”,pricelist!D175,IF(E12=”15h”,pricelist!D178,IF(E12=”15i”,pricelist!D179,IF(E12=”15j”,pricelist!D180,IF(E12=”15k”,pricelist!D181,IF(E12=”16b”,pricelist!D185,IF(E12=”16c”,pricelist!D186,IF(E12=”16d”,pricelist!D187,IF(E12=”16e”,pricelist!D188,IF(E12=”16h”,pricelist!D191,IF(E12=”16i”,pricelist!D192,IF(E12=”16j”,pricelist!D193,IF(E12=”16k”,pricelist!D194,IF(E12=”1b”,pricelist!D198,IF(E12=”17c”,pricelist!D199,IF(E12=”17d”,pricelist!D200,IF(E12=”17e”,pricelist!D201,IF(E12=”17i”,pricelist!D204,IF(E12=”17j”,pricelist!D205,IF(E12=”17k”,pricelist!D206,IF(E12=”18b”,pricelist!D207,IF(E12=”18c”,pricelist!D211,IF(E12=”18d”,pricelist!D212,IF(E12=”18e”,pricelist!D213,IF(E12=”18h”,pricelist!D214,IF(E12=”18i”,pricelist!D217,IF(E12=”18j”,pricelist!D218,IF(E12=”18k”,pricelist!D219,IF(E12=”19b”,pricelist!D220,IF(E12=”19c”,pricelist!D224,IF(E12=”19d”,pricelist!D225,IF(E12=”19e”,pricelist!D226,IF(E12=”19h”,pricelist!D227,IF(E12=”19i”,pricelist!D229,IF(E12=”19j”,pricelist!D230,IF(E12=”19k”,pricelist!D231,IF(E12=”20b”,pricelist!D232,IF(E12=”20c”,pricelist!D233,IF(E12=”20d”,pricelist!D237,IF(E12=”20e”,pricelist!D238,IF(E12=”20h”,pricelist!D239,IF(E12=”20i”,pricelist!D240,IF(E12=”20j”,pricelist!D243,IF(E12=”20k”,pricelist!D244,IF(E12=”21b”,pricelist!D245,IF(E12=”21c”,pricelist!D246,IF(E12=”21d”,pricelist!D250,IF(E12=”21e”,pricelist!D251,IF(E12=”21h”,pricelist!D252,IF(E12=”21i”,pricelist!D253,IF(E12=”21j”,pricelist!D256,IF(E12=”21k”,pricelist!D257,IF(E12=”22b”,pricelist!D258,IF(E12=”22c”,pricelist!D259,IF(E12=”22d”,pricelist!D263,IF(E12=”22e”,pricelist!D264,IF(E12=”22h”,pricelist!D265,IF(E12=”22i”,pricelist!D266,IF(E12=”22j”,pricelist!D269,IF(E12=”22k”,pricelist!D270,IF(E12=”23b”,pricelist!D271,IF(E12=”23c”,pricelist!D272,IF(E12=”23d”,pricelist!D276,IF(E12=”23e”,pricelist!D277,IF(E12=”23i”,pricelist!D278,IF(E12=”23j”,pricelist!D279,IF(E12=”23k”,pricelist!D282,IF(E12=”24b”,pricelist!D283,IF(E12=”24c”,pricelist!D284,IF(E12=”24d”,pricelist!D292,IF(E12=”24e”,pricelist!D295,IF(E12=”24h”,pricelist!D296,IF(E12=”24i”,pricelist!D297,IF(E12=”24j”,pricelist!D298,IF(E12=”24k”,pricelist!D302,IF(E12=”25b”,pricelist!D303,IF(E12=”25c”,pricelist!D304,IF(E12=”25d”,pricelist!D305,IF(E12=”25e”,pricelist!D308,IF(E12=”25h”,pricelist!D309,IF(E12=”25i”,pricelist!D310,IF(E12=”25j”,pricelist!D311,IF(E12=”25k”,pricelist!D315,IF(E12=”26b”,pricelist!D316,IF(E12=”26c”,pricelist!D317,IF(E12=”26d”,pricelist!D318,IF(E12=”26e”,pricelist!D321,IF(E12=”26h”,pricelist!D322,IF(E12=”26i”,pricelist!D323,IF(E12=”26j”,pricelist!D324,IF(E12=”26k”,pricelist!D328,IF(E12=”27b”,pricelist!D329,IF(E12=”27c”,pricelist!D330,IF(E12=”27d”,pricelist!D331,IF(E12=”27e”,pricelist!D334,IF(E12=”27h”,pricelist!D335,IF(E12=”27i”,pricelist!D336,IF(E12=”27j”,pricelist!D337,IF(E12=”27k”,pricelist!D341,IF(E12=”28b”,pricelist!D342,IF(E12=”28c”,pricelist!D343,IF(E12=”28d”,pricelist!D344,IF(E12=”28e”,pricelist!D347,IF(E12=”28h”,pricelist!D348,IF(E12=”28i”,pricelist!D349,IF(E12=”28j”,pricelist!D350,IF(E12=”28k”,pricelist!D354,IF(E12=”29b”,pricelist!D355,IF(E12=”29c”,pricelist!D356,IF(E12=”29d”,pricelist!D357,IF(E12=”29e”,pricelist!D360,IF(E12=”29i”,pricelist!D361,IF(E12=”29j”,pricelist!D362,IF(E12=”29k”,pricelist!D363,IF(E12=”30b”,pricelist!D367,IF(E12=”30c”,pricelist!D368,IF(E12=”30d”,pricelist!D369,IF(E12=”30e”,pricelist!D370,IF(E12=”30h”,pricelist!D373,IF(E12=”30i”,pricelist!D374,IF(E12=”30j”,pricelist!D375,IF(E12=”30k”,pricelist!D376,”Not Studio”))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

i use this formula but it has an error because i uses more than 64 nested..can any body help me..please..



cehj

=IF(E12=”1b”,pricelist!D3,IF(E12=”1c”,pricelist!D4,IF(E12=”1d”,pricelist!D5,IF(E12=”1e”,pricelist!D6,IF(E12=”1h”,pricelist!D9,IF(E12=”1i”,pricelist!D10,IF(E12=”1j”,pricelist!D11,IF(E12=”1k”,pricelist!D12,IF(E12=”2b”,pricelist!D16,IF(E12=”2c”,pricelist!D17,IF(E12=”2d”,pricelist!D18,IF(E12=”2e”,pricelist!D19,IF(E12=”2h”,pricelist!D22,IF(E12=”2i”,pricelist!D23,IF(E12=”2j”,pricelist!D23,IF(E12=”2k”,pricelist!D24,IF(E12=”3b”,pricelist!D29,IF(E12=”3c”,pricelist!D30,IF(E12=”3d”,pricelist!D31,IF(E12=”3e”,pricelist!D32,IF(E12=”3i”,pricelist!D33,IF(E12=”3j”,pricelist!D34,IF(E12=”3k”,pricelist!D35,IF(E12=”4b”,pricelist!D42,IF(E12=”4c”,pricelist!D43,IF(E12=”4d”,pricelist!D44,IF(E12=”4e”,pricelist!D45,IF(E12=”4h”,pricelist!D48,IF(E12=”4i”,pricelist!D49,IF(E12=”4j”,pricelist!D50,IF(E12=”4k”,pricelist!D51,IF(E12=”5b”,pricelist!D55,IF(E12=”5c”,pricelist!D56,IF(E12=”5d”,pricelist!D57,IF(E12=”5e”,pricelist!D58,IF(E12=”5h”,pricelist!D61,IF(E12=”5i”,pricelist!D62,IF(E12=”5j”,pricelist!D63,IF(E12=”5k”,pricelist!D64,IF(E12=”6b”,prcelist!D68,IF(E12=”6c”,pricelist!D69,IF(E12=”6d”,pricelist!D70,IF(E12=”6e”,pricelist!D71,IF(E12=”6h”,pricelist!D74,IF(E12=”6i”,pricelist!D75,IF(E12=”6j”,pricelist!D76,IF(E12=”6k”,pricelist!D77,IF(E12=”7b”,pricelist!D81,IF(E12=”7c”,pricelist!D82,IF(E12=”7d”,pricelist!D83,IF(E12=”7e”,pricelist!D84,IF(E12=”7h”,pricelist!D87,IF(E12=”7i”,pricelist!D88,IF(E12=”7j”,pricelist!D89,IF(E12=”7k”,pricelist!D90,IF(E12=”8b”,pricelist!D94,IF(E12=”8c”,pricelist!D95,IF(E12=”8b”,pricelist!D96,IF(E12=”8c”,pricelist!D97,IF(E12=”8d”,pricelist!D100,IF(E12=”8e”,pricelist!D101,IF(E12=”8h”,pricelist!D102,IF(E12=”8i”,pricelist!D103,IF(E12=”8j”,pricelist!D107,IF(E12=”8k”,pricelist!D108,IF(E12=”9b”,pricelist!D109,IF(E12=”9c”,pricelist!D110,IF(E12=”9d”,pricelist!D113,IF(E12=”9e”,pricelist!D114,IF(E12=”9h”,pricelist!D115,IF(E12=”9i”,pricelist!D116,IF(E12=”9j”,pricelist!D120,IF(E12=”9k”,pricelist!D121,IF(E12=”10b”,pricelist!D122,IF(E12=”10c”,pricelist!D123,IF(E12=”10d”,pricelist!D124,IF(E12=”10e”,pricelist!D126,IF(E12=”10i”,pricelist!D127,I(E12=”10j”,pricelist!D128,IF(E12=”10k”,pricelist!D129,IF(E12=”11b”,pricelist!D133,IF(E12=”11c”,pricelist!D134,IF(E12=”11d”,pricelist!D135,IF(E12=”11e”,pricelist!D136,IF(E12=”11h”,pricelist!D139,IF(E12=”11i”,pricelist!D140,IF(E12=”11j”,pricelist!D141,IF(E12=”11k”,pricelist!D142,IF(E12=”12b”,pricelist!D146,IF(E12=”12c”,pricelist!D147,IF(E12=”12d”,pricelist!D148,IF(E12=”12e”,pricelist!D149,IF(E12=”12h”,pricelist!D152,IF(E12=”12i”,pricelist!D153,IF(E12=”12j”,pricelist!D154,IF(E12=”12k”,pricelist!D155,IF(E12=”14b”,pricelist!D159,IF(E12=”14c”,pricelist!D160,IF(E12=”14d”,pricelist!D161,IF(E12=”14e”,pricelist!D162,IF(E12=”14h”,pricelist!D165,IF(E12=”14i”,pricelist!D166,IF(E12=”14j”,pricelist!D167,IF(E12=”14k”,pricelist!D168,IF(E12=”15b”,pricelist!D172,IF(E12=”15c”,pricelist!D173,IF(E12=”15d”,pricelist!D174,IF(E12=”15e”,pricelist!D175,IF(E12=”15h”,pricelist!D178,IF(E12=”15i”,pricelist!D179,IF(E12=”15j”,pricelist!D180,IF(E12=”15k”,pricelist!D181,IF(E12=”16b”,pricelist!D185,IF(E12=”16c”,pricelist!D186,IF(E12=”16d”,pricelist!D187,IF(E12=”16e”,pricelist!D188,IF(E12=”16h”,pricelist!D191,IF(E12=”16i”,pricelist!D192,IF(E12=”16j”,pricelist!D193,IF(E12=”16k”,pricelist!D194,IF(E12=”1b”,pricelist!D198,IF(E12=”17c”,pricelist!D199,IF(E12=”17d”,pricelist!D200,IF(E12=”17e”,pricelist!D201,IF(E12=”17i”,pricelist!D204,IF(E12=”17j”,pricelist!D205,IF(E12=”17k”,pricelist!D206,IF(E12=”18b”,pricelist!D207,IF(E12=”18c”,pricelist!D211,IF(E12=”18d”,pricelist!D212,IF(E12=”18e”,pricelist!D213,IF(E12=”18h”,pricelist!D214,IF(E12=”18i”,pricelist!D217,IF(E12=”18j”,pricelist!D218,IF(E12=”18k”,pricelist!D219,IF(E12=”19b”,pricelist!D220,IF(E12=”19c”,pricelist!D224,IF(E12=”19d”,pricelist!D225,IF(E12=”19e”,pricelist!D226,IF(E12=”19h”,pricelist!D227,IF(E12=”19i”,pricelist!D229,IF(E12=”19j”,pricelist!D230,IF(E12=”19k”,pricelist!D231,IF(E12=”20b”,pricelist!D232,IF(E12=”20c”,pricelist!D233,IF(E12=”20d”,pricelist!D237,IF(E12=”20e”,pricelist!D238,IF(E12=”20h”,pricelist!D239,IF(E12=”20i”,pricelist!D240,IF(E12=”20j”,pricelist!D243,IF(E12=”20k”,pricelist!D244,IF(E12=”21b”,pricelist!D245,IF(E12=”21c”,pricelist!D246,IF(E12=”21d”,pricelist!D250,IF(E12=”21e”,pricelist!D251,IF(E12=”21h”,pricelist!D252,IF(E12=”21i”,pricelist!D253,IF(E12=”21j”,pricelist!D256,IF(E12=”21k”,pricelist!D257,IF(E12=”22b”,pricelist!D258,IF(E12=”22c”,pricelist!D259,IF(E12=”22d”,pricelist!D263,IF(E12=”22e”,pricelist!D264,IF(E12=”22h”,pricelist!D265,IF(E12=”22i”,pricelist!D266,IF(E12=”22j”,pricelist!D269,IF(E12=”22k”,pricelist!D270,IF(E12=”23b”,pricelist!D271,IF(E12=”23c”,pricelist!D272,IF(E12=”23d”,pricelist!D276,IF(E12=”23e”,pricelist!D277,IF(E12=”23i”,pricelist!D278,IF(E12=”23j”,pricelist!D279,IF(E12=”23k”,pricelist!D282,IF(E12=”24b”,pricelist!D283,IF(E12=”24c”,pricelist!D284,IF(E12=”24d”,pricelist!D292,IF(E12=”24e”,pricelist!D295,IF(E12=”24h”,pricelist!D296,IF(E12=”24i”,pricelist!D297,IF(E12=”24j”,pricelist!D298,IF(E12=”24k”,pricelist!D302,IF(E12=”25b”,pricelist!D303,IF(E12=”25c”,pricelist!D304,IF(E12=”25d”,pricelist!D305,IF(E12=”25e”,pricelist!D308,IF(E12=”25h”,pricelist!D309,IF(E12=”25i”,pricelist!D310,IF(E12=”25j”,pricelist!D311,IF(E12=”25k”,pricelist!D315,IF(E12=”26b”,pricelist!D316,IF(E12=”26c”,pricelist!D317,IF(E12=”26d”,pricelist!D318,IF(E12=”26e”,pricelist!D321,IF(E12=”26h”,pricelist!D322,IF(E12=”26i”,pricelist!D323,IF(E12=”26j”,pricelist!D324,IF(E12=”26k”,pricelist!D328,IF(E12=”27b”,pricelist!D329,IF(E12=”27c”,pricelist!D330,IF(E12=”27d”,pricelist!D331,IF(E12=”27e”,pricelist!D334,IF(E12=”27h”,pricelist!D335,IF(E12=”27i”,pricelist!D336,IF(E12=”27j”,pricelist!D337,IF(E12=”27k”,pricelist!D341,IF(E12=”28b”,pricelist!D342,IF(E12=”28c”,pricelist!D343,IF(E12=”28d”,pricelist!D344,IF(E12=”28e”,pricelist!D347,IF(E12=”28h”,pricelist!D348,IF(E12=”28i”,pricelist!D349,IF(E12=”28j”,pricelist!D350,IF(E12=”28k”,pricelist!D354,IF(E12=”29b”,pricelist!D355,IF(E12=”29c”,pricelist!D356,IF(E12=”29d”,pricelist!D357,IF(E12=”29e”,pricelist!D360,IF(E12=”29i”,pricelist!D361,IF(E12=”29j”,pricelist!D362,IF(E12=”29k”,pricelist!D363,IF(E12=”30b”,pricelist!D367,IF(E12=”30c”,pricelist!D368,IF(E12=”30d”,pricelist!D369,IF(E12=”30e”,pricelist!D370,IF(E12=”30h”,pricelist!D373,IF(E12=”30i”,pricelist!D374,IF(E12=”30j”,pricelist!D375,IF(E12=”30k”,pricelist!D376,”Not Studio”))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

please help me..i need a formula to make this..



JG

Please try the following formula at the Pass? cell for the pass or fail example given:

IF(B18>=40,IF(COUNTIF(C18:H18,”>=40″)>=2,IF(COUNTIF(C18:H18,”>=30″)>=5,”Pass”,”Fail”),”Fail”),”Fail”)

The subjects A to G correspond to the range B18:H18



Phil

I am stuck. I have a column (txt) I,II,III,IV. I have a row (txt) A, B, C, D. I am trying to write a formula that captures the intersetion. Example: The intersection of I and A = 1; I and B = 1; I and C = 2; I and D = 3.

Here is my table:
A B C D
I 1 1 2 3
II 1 2 3 4
III 2 3 4 5
IV 3 4 5 5



eric

Hi,

Here’s my scenario.

I have a column containing arbitrary identifers that are combinations of letters, numbers, dashes, but also text.

For example, C1-1-Male, C1-2-Female, etc..

I need the cells in the adjacent column to read the identifiers of the cell in the same row, find that the text “Male” or “Female” is present, and output the number 0 if either condition is met, else give a “–” if not met.

Thanks!

Eric



Chris

Help! I am trying to use the if formula to assign levels to students dependant on their test scores.

I want it so that;

Less than 11 = level 3
Between 11 & 24 = level 4
Over 24 = level 5.

Can anyone help?

Many thanks,



Lee

Hello,
I am trying to figure out how i can get exel to recoganize a certain category numbers and total them all together at the bottom of my sheet or just keep a running total beside the latest category number as you go down the sheet.



Viswa Ghosh

Please help. I have a very similar problem, but am not able to resolve it with COUNTIF, FREQUENCY, etc.

My problem is to count ALL such events when:
1) The value under Column-A is less than 0.5, the value under Column-B is less than 11.25;
2) Similarly, number of events when the value under Column-A is less than 1.5, and the value under Column-B is less than 11.25; and so on.

Viswa
P.S.
I have tried the following, but these are NOT working:
=IF(AND($B$4:$B$139214<H$1,$C$4:$C$139214<$G4),FREQUENCY($B$4:$B$139214,H$1),0)

=IF($B$4:$B$139214<H$1,IF($C$4:$C$139214<$G4,FREQUENCY($B$4:$B$139214,H$1),0),0)

=IF($B$4:$B$23<H$1,IF($C$4:$C$23<$G10,FREQUENCY($B$4:$B$23,H$1),0),0)



udhiiydwkaih

bchwfvhrkspp