SOLVED

IF formulas

Copper Contributor

Hi, I'm looking for the right formula in the yellow column to automatically calculate the cost in stead of manual input

Ticket AmountcostsNett
ticket price
 insert which formula IF function that automatically populates the correct costs? 
21,003,0018,00
42,006,0036,00
63,009,0054,00
84,0012,0072,00
105,0015,0090,00
126,0018,00108,00
147,0021,00126,00
168,0024,00144,00
189,0027,00162,00
210,0030,00180,00
126,0021,00105,00
252,0042,00210,00
378,0063,00315,00
504,0084,00420,00

 

7 Replies

@Huub_Maas 

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.

@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?

@Huub_Maas 

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...

best response confirmed by Huub_Maas (Copper Contributor)
Solution

@Huub_Maas 

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

 

@mathetes 

Hi, 

Many thanks. 

The IFS function is the right solution for this case.

Problem solved !! 

@Huub_Maas 

If you example is correct Ticket Amount 126 could have two different costs, logic which one to select is not defined

image.png 

Thanks for your observation. The lower cost amount is incorrect in this example. In the meantime I've found the solution by using the IFS function.
1 best response

Accepted Solutions
best response confirmed by Huub_Maas (Copper Contributor)
Solution

@Huub_Maas 

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

 

View solution in original post