IF Function Does Not Calculate Properly When Adding a 3rd IF formula

[EverClever]

Hello All,

The following equation calculates the correct answer, 2264, where D11 = 175 , D10 = 13%:

=IF('Data Input'!$D$9="Female","",((370+(21.6*'Data Input'!$D$11*1-$D$10))/2.2)*IF('Data Input'!$D$12="10 - 30",1.2))

but the following modified equation does not calculate the correct answer, 1886 (it calculates 0, an incorrect answer), where D11 still = 175 and D10 still = 13%:

=IF('Data Input'!$D$9="Female","",((370+(21.6*'Data Input'!$D$11*1-$D$10))/2.2)*IF('Data Input'!$D$12="0",1)*IF('Data Input'!$D$12="10 - 30",1.2))

Thank you,

Patrick

[JohnTopley]

Try

=IF('Data Input'!$D$9="Female","",((370+(21.6*'Data Input'!$D$11*1-$D$11))/2.2)*IF('Data Input'!$D$12="0",1,0)*IF('Data Input'!$D$12="10 - 30",1.2,1))


No FALSE conditions specified in your original formula: guessing at the above

[leelnich]

Hi all- @EverClever, you're testing the same cell ($D$12) for two different values. The tests can't BOTH be TRUE, so at least one multiplying clause yields 0. Combine them by nesting one within the other. IF(TRUE,X,else IF(TRUE,Y)) as follows:

=IF('Data Input'!$D$9="Female","",((370+(21.6*'Data Input'!$D$11*1-$D$10))/2.2)*IF('Data Input'!$D$12="10 - 30",1.2,IF('Data Input'!$D$12="0",1)))

[leelnich]

BTW, is this correct?:
=IF('Data Input'!$D$9="Female","",((370+(21.6*'Data Input'!$D$11*1-$D$10))/2.2)*IF...

Should it be?:
=IF('Data Input'!$D$9="Female","",((370+(21.6*'Data Input'!$D$11*(1-$D$10)))/2.2)*IF...

or just:
=IF('Data Input'!$D$9="Female","",((370+(21.6*'Data Input'!$D$11)-$D$10)/2.2)*IF...

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[EverClever]

D12 actually has 4 different values because it is a drop down list. So I cut and pasted leelnich's formula below and it only got 1 answer correctly and the other returned 0. John Topley's formula also did not work.

[AliGW]

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[JohnTopley]

My formula did work: I got a result of 1886

[EverClever]

John Topley,

Your formula does not work when I insert '10 - 30 into the Data Input worksheet page cell D12, which is the 2nd item on my 4 item drop down list.

[AliGW]

EverClever - please attach YOUR workbook here so that this can be cleared up. It is either user error on your part or something about YOUR file that is messing this up.

[EverClever]

AliGW,

My spreadsheet sample is attached, labeled EverClever-Sample

[JohnTopley]

It doesn't work because I set the FALSE to 0 on the previous D12 test: a little testing would have highlighted the "error".

=IF('Data Input'!$D$9="Female","",((370+(21.6*'Data Input'!$D$11*1-$D$11))/2.2)*IF('Data Input'!$D$12="0",1,1)*IF('Data Input'!$D$12="10 - 30",1.2,1))

As previously pointed out if you have multiple choices for D12, then perhaps a "lookup" of some kind is required to determine what we multiply by (rather than multiple IFs).

As Ali's has requested twice, a workbook is required so we know EXACTLY what is required

[AliGW]

See post #10, John.

[EverClever]

John Topley,

Thank you for your continued help! I just sent AliGW a sample worksheet. I have never used the LOOKUP Function.

[AliGW]

Not sent to me! Attached above - thanks!

[JohnTopley]

With your sample to have not given the mutiplying "factors" applicable to each age group: so no, you will not get all the answers.

[EverClever]

I was just trying to build the formula 1 section at a time to see if it worked before adding on. My original formula did contain 2 of the 4 possibilities, 0 and 10 - 30 but obviously not formatted correctly.

[JohnTopley]

Create a table as below

0 1
10 - 30 1.2
35 - 55 1.35
60 + 1.5

I created it F12:G15 of "Data Input!"

then use ..

=IF('Data Input'!$D$9="Female","",((370+(21.6*'Data Input'!$D$11*1-$D$11))/2.2)*VLOOKUP('Data Input'!$D$12,'Data Input'!$F$12:$G$15,2,0))

[EverClever]

John Topley,

That worked! Thank you so much....I would have never figured that out on my own

Patrick

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[EverClever]

Did not see an option on Thread Tools to mark "Solved"?