I am currently having hard time creating a formula for my sheet to record values when the following conditions will be met:
I want to give 15% discount from the original price if a Person purchased their ticket online (Online = TRUE), and only applicable for customers who is atleast 50 years old, kindly list the new calculated discounted price in the Discounted Price Column
I have tried creating a formula but I got formula parse error.
Formula used : =IF((B2=TRUE AND D2>50),(E2*0.85),E2)
This is my sample data in the sheet and the expected output on Discounted Price column. Any help will be appreciated.
| Person | Online | Physical Store | Age | Original Price | Discounted Price |
|---|---|---|---|---|---|
| A | TRUE | FALSE | 67 | 1000 | 850 |
| B | TRUE | FALSE | 16 | 1000 | 1000 |
| C | FALSE | TRUE | 24 | 1000 | 1000 |
| D | TRUE | FALSE | 52 | 1000 | 850 |
| E | FALSE | TRUE | 60 | 1000 | 1000 |
CodePudding user response：
Your formula is almost correct except for the AND operator, the syntax for using AND is as follows: AND(logical_expression1, [logical_expression2, ...]).
I have replicated your data and fix the formula. Please see formula and desired output below
Formula:
=IF(AND(B2=TRUE,D2>50),(E2*0.85),E2)
Data and Output:
References:
https://support.google.com/docs/answer/7014145 https://support.google.com/docs/answer/3093301?hl=en
CodePudding user response：
use:
=INDEX(IF(B2:B="",,IF((B2:B=TRUE)*(D2:D>50), (E2:E*0.85), E2:E))