A simple formula for spreadsheet
Elo
-
Elo -
Elo -
Hello,
I would like to calculate my expenses as follows:
Column A - Date
Column B - Name
Column C - Amount
Column E - Number of Meal Vouchers "8€"
Column F - For 1 Meal Voucher employer part "5€"
Column G - For 1 Meal Voucher personal part "3€"
Column H - Total
Thank you
I would like to calculate my expenses as follows:
Column A - Date
Column B - Name
Column C - Amount
Column E - Number of Meal Vouchers "8€"
Column F - For 1 Meal Voucher employer part "5€"
Column G - For 1 Meal Voucher personal part "3€"
Column H - Total
Thank you
Configuration: Windows XP Firefox 3.5.7
20 answers
-
Hello,
if I understand your problem correctly
in column E2 you put your number of tickets
in F2 you put =E2*5
in G2 you put =E2*3
So in F you have the employer's total and in G you have your total
--
Every problem has a solution...you just have to be persistent. -
It might not have been clear enough.
So I would like to know what my total expenses are after deducting the employer's share based on the number of meal vouchers.
Thank you. -
What is the difference between columns C and H?
--
Retirement is great! Especially in the Caribbean...
☻ Raymond ♂ -
Hello
In C, it’s the amount of my invoice and in H, it’s the total amount minus the employer's share of the meal ticket based on the number.
Thank you -
So G and H are the same?!
--
Retirement is great! Especially in the Antilles... :-)
☻ Raymond ♂ -
Hello,
I am rephrasing my question
I am going to a restaurant with a friend.
I pay €20 with 2 restaurant vouchers. How much does she owe me?
She pays €20 with my 2 restaurant vouchers. How much do I owe her?
How to do it in the form of a spreadsheet?
Best regards, Elodie. -
in E2: =FLOOR(C2/8;0)
in F2:=E2*5
in G2:=E2*3
but after I don't really understand the context, it's tickets worth how much?
what is the employer's share and what is your share?
--
Are you having problems with IE8? I have THE solution for you, install Firefox (search on Google)
--
Are you having problems with IE8? I have THE solution for you, install Firefox (search on Google) -
Bonjour Elodie,
Pour mettre en place votre demande sous forme de tableur, voici comment structurer les informations :
1. **Colonnes du Tableur :**
- **A** : Description
- **B** : Prix total payé
- **C** : Nombre de tickets restaurant
- **D** : Montant par ticket (8€)
- **E** : Contribution de chacun (5€ pour l'employeur et 3€ pour vous)
- **F** : Montant final dû ou à recevoir
2. **Exemples de calculs :**
- **Première ligne (allant au restaurant avec votre amie):**
- **A2** : "Vous payez"
- **B2** : 17€
- **C2** : 2
- **D2** : 8€
- **E2** : 3€
- **F2** : "=(B2-(C2*E2))" (Votre amie vous doit)
- **Deuxième ligne (votre amie paye avec vos tickets):**
- **A3** : "Votre amie paye"
- **B3** : 17€
- **C3** : 2
- **D3** : 8€
- **E3** : 3€
- **F3** : "=(B3-(C3*E3))" (Vous lui devez)
3. **Formules additionnelles :**
- Pour chaque ligne, vous calculerez combien chacun doit en fonction des prix et des tickets. Assurez-vous d'utiliser les cellules correctement dans vos formules pour que les résultats soient automatiquement mis à jour en fonction des valeurs entrées. Ainsi, vous pourrez suivre facilement qui doit quoi, selon le nombre de tickets restaurant utilisés.
Cordialement.-
Good evening
I see it this way: restaurant cost - sum of the tickets, the remainder divided by the number of guests
now we need to determine
1) number of guests who participated in the form of tickets
2) if the ticket value is the same for everyone
this can be done with a proportion calculation, the main thing is to have all the parameters in order to always have 100%
-
-
Hello Élodie.
That's why it's important to be precise and use the right terms...
Don't forget that while you may know exactly what you're talking about, those reading you know nothing, and you need to explain everything to them in detail!
Thus, after 9 exchanged messages, we realize that your initial presentation
Column A - Date
Column B - Name
Column C - Amount
Column E - Number of Restaurant Tickets "8€"
Column F - For 1 Employer Share Restaurant Ticket "5€"
Column G - For 1 Personal Share Restaurant Ticket "3€"
Column H - Total
should have been worded
Column A - Date
Column B - Name
Column C - Restaurant Invoice Amount
Column E - Number of Restaurant Tickets "8€" given
Column F - Employer Share (5€ per ticket)
Column G - Personal Share (3€ per ticket)
Column H - Balance to be added
So I would tend to put
* in column H the difference between the invoice and the value of the tickets =C2-GF2-G2
* in column I what she owes me, half of the invoice =IF(E2*8>C2/2,C2/2,0)
* in column J what I owe her, the difference between half of the invoice and my tickets =IF(E2*8<c2 />https://www.cjoint.com/?cewM4oseiI
--
It's great, retirement! Especially in the Caribbean... :-)
☻ Raymond ♂-
Hello Raymond
I realize that we are moving from subtraction to calculating proportions with variable data; indeed, the only fixed value is the expense; now to be able to put this into a spreadsheet, it is vital to know the range of "employer participation" because this is essential to determine the amount actually spent by each guest
Given that: if the ticket value is "standard": it's not difficult, but if the ticket value is variable (tickets at 7, 8, 10 $ and employer contributions are different), we need to build a spreadsheet with multiple entries that ("puts into equation") all possible scenarios (not quite headache-inducing, but almost)
Here is an interesting topic; basically, it "suffices" to figure out each person's share based on their contribution
It's raining here, we would need "tropical vibes"
Have a good day
-
-
I would like one of those Doliprane :/
Might as well do this in VBA, it's less annoying in terms of formulae
--
Are you having problems with IE8? I’ll give you THE solution, install Firefox (search on Google) -
Hello,
Thank you for trying to solve my problem.
In the case where I pay €17 with my 2 tickets, she owes me €3.50.
In the case where she pays €17 with my 2 tickets, I owe her €2.50.
In the case where I pay €17 with 1 of my tickets, she owes me €6.
In the case where she pays €17 with 1 of my tickets, I owe her €3.
Thank you -
Hi there!
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂ -
Hello,
Well, I can see that it doesn't seem to be as simple as it is for you.
I will explain how I see my calculation in the hope that this time it will be suitable for putting it into a spreadsheet.
In the case where I pay €17 with my 2 tickets, she owes me €3.50.
((17 - (2 * 8) / 2) = €0.50 + ((2 * 3) / 2) = €3 => €3.50
(the amount - number of tickets * ticket price) / 2 "divided by two because we share" + (number of tickets * my personal share) / 2 "divided by two because we share"
In the case where she pays €17 with my 2 tickets, I owe her €2.50.
(the amount - number of tickets * ticket price) / 2 "divided by two because we share" - (number of tickets * my personal share) / 2 "divided by two because we share"
Thank you -
Hello,
I think the best thing is for me to attach my file so you can see it and make the necessary modifications for it to work.
Here it is.
http://www.cijoint.fr/cjlink.php?file=cj201002/cijks3F25F.xlsx-
Hello,
here is your file as I understand it
http://www.cijoint.fr/cjlink.php?file=cj201002/cijiiWmLB8.xlsx
And by the way, I am really a good sport. Since the beginning of your message, you've been making us work for nothing, either because you're only giving a few details, or because all of a sudden (Post 18) you decide to tell us that you are sharing the bill. You know, as Raymond mentioned to you so well, you know what you're talking about and you know what you want to do... not us.
Thank you
Good night
--
Every problem has a solution... you just have to be persistent.- Hello gilou
When you get a chance, explain to him (because I'm done with this discussion) the difference between spreadsheet and abacus
and make him explain why she sometimes pays 3.50 € and sometimes 2.50 € for the same invoice of 17.00 €!
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
-
-
Hello
I made a directly usable template for you
the only columns to use are: who? ; number of tickets. and the 2 value parameters of the ticket; in other words, the first 4 columns then you enter the invoice amount (in red) that's all
the following columns are not protected > do not enter anything
nb) the share of the amount owed or to be recovered is calculated based on what the ticket really cost you
I prepared it for about twelve guests
if that works for you, let me know
http://www.cijoint.fr/cjlink.php?file=cj201002/cijSYP1iIJ.xls -
Hello,
A big thank you for Ricky38, gilou...
In Ricky38's folder, it's exactly what I'm looking to have but I don't understand why I have a negative value in cell K8, I think personal share needs to be taken into account...
It's in L8 because it's Sophie who owes me since my personal share of €3 is higher than her share "1€/2=0.5"
There must be something in the formula like > or < or = so that depending on the personal share it calculates in one column or the other.
Thank you. -
Hello,
Nothing beats a beautiful table revamped with colors and explanations.
I think there will be a solution to my problem there.
http://www.cijoint.fr/cjlink.php?file=cj201002/cijpIM1COm.xlsx
Best regards, Elodie. -
All of this is perfectly artificial and abnormal.
Sophie eats as much as you do, so she pays half of the bill.
You pay the other half with the means of payment you have: credit card, bank check, restaurant voucher, or cash.
The employer is not hers, but yours, and the regulation is very strict: it states that restaurant vouchers are intended solely for the beneficiary to use for their personal meals, and this, during the current year; the restaurateur must not give change on a voucher; one cannot buy anything other than food with these vouchers; they cannot be transferred or sold to a third party.
I had said that I was done with this discussion, but I had to warn you.
Moreover, the idea of colors is appealing in principle, but yellow and orange on a white background is quite hard to read...
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂ -
Hello,
I just need a formula, if someone can help me with this problem.
So, http://www.cijoint.fr/cjlink.php?file=cj201002/cijpIM1COm.xlsx
Best regards, Elodie. -
