Use of two formulas

[ryanrimsky]

Hi,

I'm trying to relieve some stress and make our spreadsheets easier.
We have a weird process, so the payment method (Card, Direct Debit, Apple Pay, Bank Transfer, etc) determines where we put the gross value in.
Card and Apple pay payments go through as 'Card'.
Direct Debit and Bank Transfer go through as 'Bank'.

I want to add a formula to automatically calculate the VAT and NET from whichever cell is filled with the gross value.
Sorry if this is confusing or I haven't explained it too well.

Book1
	A	B	C	D	E	F	G
1	Date	Payment Details	Payment Method	Bank	Card	VAT	Net
2	06/04/2023	Example 1	Card		£ 100.00
3	06/04/2023	Example 2	Direct Debit	£ 20.00
4	07/04/2023	Example 3	Apple Pay		£ 50.00
5	08/04/2023	Example 4	Bank Transfer	£ 200.00
Sheet1

 

[etaf]

if the other columns are blank - how about using MAX() or SUM()
and then the formula to either add vat or calculate

not sure what you need here

if its 20% already included
then
Max(D2,E2)

Book6
	A	B	C	D	E	F	G
1	Date	Payment Details	Payment Method	Bank	Card	VAT	Net
2	45022	Example 1	Card		100	16.6666667	83.3333333
3	45022	Example 2	Direct Debit	20		3.33333333	16.6666667
4	45023	Example 3	Apple Pay		50	8.33333333	41.6666667
5	45024	Example 4	Bank Transfer	200		33.3333333	166.666667
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=(MAX(D2:E2)/1.2)*0.2
G2:G5	G2	=MAX(D2:E2)/1.2