May 25 2021 12:18 PM
Hi,
I'm trying to create a nested IF statement as follows, the following formula works correctly and as expected:
=IF($D$2="points",$C$5*$G$7,IF($D$2="Discount or GWP",$C$6,IF($D$2="Cash Reward or Cashback",$C$7*$G$7*100)))
However when I try and use the following formula, the only change is the bit in Orange/ Bold the formula then triggers and error and doesn't work:
=IF($C$2="Points",$C$6*'Business Case Refs'!B2,IF($C$2="Discount or GWP",$C$7,IF($C$2="Cash Reward or Cashback",$C$9*'Business Case Refs'!B2*100)))
I'm not entirely sure why this doesn't carry over, the cells are all the same format (Currency) and the only obvious thing I can see if that the cell is on another sheet but I don't think that should cause a problem?
Anything I can try?
Thanks
May 25 2021 01:03 PM
The formula is OK. Make sure that you have spelled the sheet name correctly. Could it perhaps be "Business Case Refs " (with a space at the end) instead of "Business Case Refs"?
May 25 2021 01:19 PM
Which kind of error do you have?
Formulas have bit different logic.
First
=IF($D$2="points",...,IF($D$2=...,$C$6,IF($D$2=...,$C$7*...)))
Second
=IF($C$2="Points",...,IF($C$2=...,$C$7,IF($C$2=...,$C$9*'...)))
May 25 2021 01:20 PM
May 25 2021 01:50 PM
With wrong reference you'll have #REF! error. With #VALUE! it's something else, e.g. you try to multiply text on number.