Jun 26 2022 09:00 AM
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 |
Jun 26 2022 10:22 AM
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.
Jun 27 2022 11:16 AM
@Hans Vogelaar
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?
Jun 27 2022 11:25 AM - edited Jun 27 2022 11:39 AM
That would look like
=IF(ticketprice=21;3;IF(ticketprice=42;6;IF(ticketprice=63;9)))
or
=IFS(ticketprice=21;3;ticketprice=42;6;ticketprice=63;9)
But it would become a very long formula. Besides, as I pointed out, your sample table has costs = 18 AND costs = 21 for ticketprice = 126...
Jun 27 2022 11:30 AM
SolutionI'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
Jun 27 2022 01:04 PM
Jun 27 2022 02:04 PM
If you example is correct Ticket Amount 126 could have two different costs, logic which one to select is not defined
Jun 27 2022 11:10 PM
Jun 27 2022 11:30 AM
SolutionI'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