HELP With IF command and formulas

[JamieN]

Hi,

Bit of a noob when it comes to Excel (been quite a few years) and setting up a quantity spreadsheet for work.
I have created an IF command that returns #VALUE! errors if the result is false. Hoping its a simple error with the format of my formula and chasing some help please.

I have attached the spreadsheet in question, but basically I need the following:

1. Result in F44 to equal ((E44-C35)*C44) if the data entered in D44 is '5'.
2. If the data entered in D44 is not '5' I would like a blank cell returned.
3. Also, on top of that, if the data entered in L44 is '2' I need to add (C44*C35) to the result in step 1.

Currently if I enter anything other then a 5 in cell D44, I get a #VALUE! error.

Please help!!

Thanks, Jamie

[bebo021999]

Without IF, it may be shorter:
In F44:
=(($L44=2)*$C$35+($D44=LEFT(F$43)+0)*($E44-$C$35))*$C44

Drag accross to G and H

[JamieN]

Thanks bebo.

That formula did work with some alterations (=(($L44=2)*$C$35+($D44=LEFT(D$44)+0)*($E44-$C$35))*$C44).

However, I should have mentioned I require columns F, G & H to return results based on whether 3, 4 or 5 is entered in column D.

In column F, I need results if a 5 is entered at column D. And a blank cell returned in columns G & H.
In column G, I need results if a 4 is entered at column D. And a blank cell returned in columns F & H.
In column H, I need results if a 3 is entered at column D. And a blank cell returned in columns F & G.

[JeteMc]

Building on bebo's formula try pasting the following into cell F44 and then drag the fill handle across to cell H44:

Please Login or Register  to view this content.

Let us know if you have any questions.