IF Function Queston

[al97233]

I currently have the following formula:
=IF($X$41<10,$X$43*AG70*$V$62,IF($X$41>9,$X$43*AG70*$V$63))

I would like to add a formula that if $X$43 is less than 1 use $CG$43 (this cell contains a sum of other cells). If $X$43 is greater than 1 than use the formula as it is.

Any ideas how i can achieve this?

 

[al97233]

Still does not work if $X$41 is less than 10

 

[Jonmo1]

Still does not work if $X$41 is less than 10

The last post didn't offer a solution, it only explained why the previous formula didn't work...


Perhaps it's time to take a step back, and take a deep breath..

Rather than posting formulas that don't work, and trying to fix them..
Try explaining in words exactly what you want your formula to do.


If X41 is < 10 AND X42 IS a number, then do .....
If X41 is < 10 AND X42 is NOT a number, then do....
If X41 is >= 10 AND X42 IS a number, then do.....
If X41 is >=10 AND X42 is NOT a number, then do....

 

[Aladin Akyurek]

I currently have the following formula:
=IF($X$41<10,$X$43*AG70*$V$62,IF($X$41>9,$X$43*AG70*$V$63))

I would like to add a formula that if $X$43 is less than 1 use $CG$43 (this cell contains a sum of other cells). If $X$43 is greater than 1 than use the formula as it is.

Any ideas how i can achieve this?

The specs seem to read...

=AG70 * IF($X$43 < 1, $CG$43, $X$43) * IF($X$1 > 9, $V$63, $V$62)

If this is not what it should be, try to elaborate in words and also indicate whether any of the relevant cells contain formulas.

 

[al97233]

I apologize for making this so confusing. I am unable to post a copy of the spreadsheet using excel jeanie because the sheet is to large (exceeds 500 cells) what i am trying to accomplish is this:

Cell $X$41 must contain a number
If that number exceeds 10 the following action should happen.
Cell $X$42 is multiplied by $V$59 if $X$41 is less than 10 than $X$42 is multiplied by $V$58
but Cell $X$42 may or may not contain a number
If $X$42 does not contain a number than the number contained in $CG$42 is used
The balance of the formula is basically just multiplying the results although the same principle applies to Cells $X$43 & $CG$43 in place of $X$42 & $CG$42

I hope this makes sense.
I would be happy to e-mail the spreadsheet if anyone would like.

 

[Jonmo1]

OK, getting there...

What if X41 is EXACTLY 10 (not less than, not greater than) ?

If $X$42 does not contain a number than the number contained in $CG$42 is used

CG42 would be used for what? Instead of X42 ?


Perhaps this

=IF(X41>10,V59,V58)*IF(ISNUMBER(X42),X42,CG42)

 

[al97233]

I should have said equal to or greater than 10.
and yes use CG42 in place of X42

 

[al97233]

=IF($X$41<10,$X$43*AG70*$V$62,IF(AND($X$41>9,$X$43>1),$X$43*AG70*$V$63,$CG$43*AG70*$V$63))

this formula was originally posted by Ziagham and it seemed to work fine. I had to make minor alterations because I had to insert cells but it still seemed to work well and now it dose not. i have looked it over and cant see what I may have done to it.

 

[Jonmo1]

=IF($X$41<10,$X$43*AG70*$V$62,IF(AND($X$41>9,$X$43>1),$X$43*AG70*$V$63,$CG$43*AG70*$V$63))

this formula was originally posted by Ziagham and it seemed to work fine. I had to make minor alterations because I had to insert cells but it still seemed to work well and now it dose not. i have looked it over and cant see what I may have done to it.

This is the problem (not with the formula, but with they way you are posting).

Your words say one thing, but you post formulas that aren't even close to what you are describing.

The formula I posted in post #15 does EXACTLY what you described in post #14.
(with the exception of the variance what to do if x41 is exactly 10)

But what you just posted in #17 is not even close to what you described in post #14

 

[Aladin Akyurek]

I apologize for making this so confusing. I am unable to post a copy of the spreadsheet using excel jeanie because the sheet is to large (exceeds 500 cells) what i am trying to accomplish is this:

Cell $X$41 must contain a number
If that number exceeds 10 the following action should happen.
Cell $X$42 is multiplied by $V$59 if $X$41 is less than 10 than $X$42 is multiplied by $V$58
but Cell $X$42 may or may not contain a number
If $X$42 does not contain a number than the number contained in $CG$42 is used
The balance of the formula is basically just multiplying the results although the same principle applies to Cells $X$43 & $CG$43 in place of $X$42 & $CG$42

I hope this makes sense.
I would be happy to e-mail the spreadsheet if anyone would like.

Changing cell addresses contributes to the confusion...

=IF(ISNUMBER($X$41),
    IF(ISNUMBER($X$42),$X$42,$CG$42)*
      IF($X$41>=10,$V$59,$V$58),0)

 

[Ingolf]

Hi,
From where is $X$42 (is a typo?!) couse in original formula is
=IF($X$41<10,$X$43*AG70*$V$62,IF($X$41>9,$X$43*AG70*$V$63))

@Zaigham
Anyway how it work this formula:
=IF($X$41<10,$X$43*AG70*$V$62,IF(AND($X$41>9,$X$43>1),$X$43*AG70*$V$63,$CG$43*AG70*$V$63)) <!-- / message -->
because don't respect this:
if $X$43 is less than 1 use $CG$43 (this cell contains a sum of other cells). If $X$43 is greater than 1 than use the formula as it is.