Forum Discussion
IF formulas
Hi, I'm looking for the right formula in the yellow column to automatically calculate the cost in stead of manual input
| Ticket Amount | costs | Nett ticket price |
| insert which formula IF function that automatically populates the correct costs? | ||
| 21,00 | 3,00 | 18,00 |
| 42,00 | 6,00 | 36,00 |
| 63,00 | 9,00 | 54,00 |
| 84,00 | 12,00 | 72,00 |
| 105,00 | 15,00 | 90,00 |
| 126,00 | 18,00 | 108,00 |
| 147,00 | 21,00 | 126,00 |
| 168,00 | 24,00 | 144,00 |
| 189,00 | 27,00 | 162,00 |
| 210,00 | 30,00 | 180,00 |
| 126,00 | 21,00 | 105,00 |
| 252,00 | 42,00 | 210,00 |
| 378,00 | 63,00 | 315,00 |
| 504,00 | 84,00 | 420,00 |
I'll jump in just to suggest that if that sample you gave is representative, then you need the IFS function, as in
=IFS(ticketprice=21,3,ticketprice=42,6,ticketprice=63,9.....)
but I'll also offer that (at least given what you've shown) you could also just say
=ticketprice/7
which leads me to ask "What is this really doing?" What is the mathematical relationship between the columns on each row, and possibly even between successive rows?"
https://exceljet.net/excel-functions/excel-ifs-function
7 Replies
I don't see a yellow column, but assuming that Ticket Amount is in A2 and down, and Nett price is in C2 and down, enter the formula =C2-A2 in B2, then fill down.
The costs don't appear to be consistent: for a ticket amount of 126, you have both 18 and 21 as costs.
HansVogelaar
Thanks Hans. What I'm looking for is an IF formula which says e.g. =IF(ticketprice=21;3),IF(ticketprice=42;6), IF(ticket-rice=63;9),.........etc.I've tried this, but the result is "VALUE"
What is a correct IF formula for this solution?
I'll jump in just to suggest that if that sample you gave is representative, then you need the IFS function, as in
=IFS(ticketprice=21,3,ticketprice=42,6,ticketprice=63,9.....)
but I'll also offer that (at least given what you've shown) you could also just say
=ticketprice/7
which leads me to ask "What is this really doing?" What is the mathematical relationship between the columns on each row, and possibly even between successive rows?"
https://exceljet.net/excel-functions/excel-ifs-function
