Formule pour calculer prix HT+TVA+TTC Résolu/Fermé

Question

Bonjour à tous

ayant cherché pendant 1, je tourne toujours en ronde...

voila je suis sur un tableau de calcul excel a trois colonne:

C8: prix HT
D8: TVA
E8: prix TTC

B23 = la TVA 

je souhaite calculer, a partir d'une des 3 valeurs, les 2 autres en les affichant dans leur case respective.

c-a-d que si l'on entre un prix HT, le tableau calcul la part de TVA ET le prix TTC
ou si l'on entre le prix TTC, excel calcul toujours la TVA et le prix HT
donc les deux cellules modifiable sont C8 ET E8

voici la formule que j'ai élaboré dans D8
=SI(ESTVIDE(E8);C8*$B$23;SI(ESTVIDE(C8);E8-E8/(1+$B$23);""))

le problème c'est qu'avec cette formule, je n'ai que TTC+TVA ou HT+TVA mais pas les 3 et si comme ma formule prend en compte C D E je ne plus faire d'autre formule sinon il y a conflit.


Merci d'avance à ceux qui pourrons m'aiguiller.

Mike-31 · Accepted Answer

Re,   

travailles tu sur le fichier que je t'ai renvoyé ?   

sinon télécharge celui ci et teste le sans rien toucher, juste des saisies en C et E    

https://www.cjoint.com/c/CEfrLhsmSLx   

je viens de l'enregistrer sous 97-2003 et il fonctionne également sans problème sur Excel 2003 comme 2007 et 2010 teste les deux versions

https://www.cjoint.com/c/CEfrPiFNyli  
   
A+   
Mike-31   

Une période d'échec est un moment rêvé pour semer les graines du savoir.

Mike-31 · Answer

Salut,

pas très clair comme explication, c'est pour t'aider à un devoir

bke_cana · Answer

désolé pour l'explication ...  

je vais essayer de faire mieux. 
il s'agit d'un modèle personnel qui me permet de faire un suivi de différents achat et selon où j'achète le prix unitaire des articles sur la facture est parfois exprimé en TTC parfois en HT. Donc je souhait entrer l'un ou l'autre et que la formule calcul automatiquement le reste 

     C     |    D    |      E      | 
--------------------------------- 
prix HT  | TVA   | prix TTC | 
-------------------------------- 
      10   | 1.96  |   11.96                       <=exemple 1 on rentre le prix HT 
      10     |   1.96|   11.96                     <= exemple 2 on rentre le prix TTC 
                                 
                                      
pour l'exemple 1: je veux déduire la TVA puis le prix TTC en fonction du pris HT 
pour l'exemple 2: je veux déduire la TVA puis le prix HT en fonction du prix TTC 



en fait, je veux que seul les colonnes prix HT (C) et prix TTC (E) soit modifiable. 

je peux t'envoyer le fichier si ca t'intéresse ou si ca peut t'aider a mieux me comprendre. 

ps: désolé pour la mise en page, je joint un lien du screen shot du tableau:http://img11.hostingpics.net/pics/801877Sanstitre.png
cdlt.

Mike-31 · Answer

Re,

si je reprends tes explications, en B23 tu as ton taux de TVA exemple 19,6% 
en C8 tu as ton prix HT pour calculer ta TVA en D8 tu saisis cette formule =C8*B23 et en E8 pour calculer le prix TTC cette formule =C8+D8 ou directement avec cette formule =C8*(1+B23)

si tu part du prix TTC en E8 pour calculer le prix HT =E8/(1+B23)
et pour la TVA =E8-C8

par contre si en B23 le taux de TVA est saisi sous cette forme 19,6 la formule pour calculer la TVA en D8 à partir d'un prix HT =C8*B23/100 et le prix TTC =C8*(1+B23/100)
 
si ce n'est pas cela, tu peux joindre ton fichier effectivement avec ce lien 
https://www.cjoint.com/

bke_cana · Answer

re 

j'ai pas de souci pour calculer une TVA ou un prix HT et TTC, ce qui me bloque, c'est de trouver une formule commune pour les deux cas de figure. 

si je rentre le prix TTC, hop ca déduit la TVA et puis ca soustrait la TVA au TTC pour obtenir le HT 

et vis versa  

je vais traduire la formule que je recherche: 

=SI( on rentre le prix HT; alors la TVA= 19.6%*HT ET le TTC= HT+TVA; SINON SI on rentre le prix TTC alors la TVA= 19.6% du TTC ET HT=TTC-TVA; sinon rien) 

donc une fonction qui prend en compte les 2 cas de figure. 

voila le fichier en ligne: http://cjoint.com/data/0EewAqLtpyh.htm

merci de votre patience.

Mike-31 · Answer

Re,  

mes formules le font, mais le problème tu ne peut conserver une formule dans une cellule dans laquelle tu saisis une valeur, ou ton tableau ne servira qu'une seule fois et ne permettra pas d'erreur, si je comprends bien ton tableau il s'agit des colonnes C et E.   
si tu tiens à cette gestion du fichier il faut passer par le vba, à ce sujet  
dans un module du visual basic tu as un début de fonction qui ne sert à rien  


A+  
Mike-31  

Une période d'échec est un moment rêvé pour semer les graines du savoir.

bke_cana · Answer

oulala

j'ai appris vite fait le VBA en cours mais c'est le langage que je déteste ^^ et puis ca fait 5, 6 ans maintenant.

si préférerai rester sur un tableau excel si possible.

je crois que tu as cerné le problème. écrire une valeur dans une cellule prise en compte par la fonction... 

ce que je souhaiterai obtenir, c'est qu'en rentrant 1 seul valeur ( TTC ou HT) les autres soient calculées. donc il faudrait imbriquer deux fonction (1 pour chaque cas: TTC ou HT)

ou alors il faudrait pouvoir affiché par la même fonction les 2 resultat dans 2 cellules différente(TVA TTC ou TVA HT), mais là je n'ai rien trouvé non plus.

Mike-31 · Answer

Re, Fait un clic droit sur l'onglet de ta feuille et colle ce code dans le Visual Basic Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Not Intersect(Selection, Range("C8:C20")) Is Nothing Then Target.Select If ActiveCell > 0 And ActiveCell.Column = 3 Then ActiveCell.Offset(0, 2) = ActiveCell * (1 + [B23]) ActiveCell.Offset(0, 2).Select Exit Sub End If End If If Not Intersect(Selection, Range("E8:E20")) Is Nothing Then Target.Select If ActiveCell > 0 And ActiveCell.Column = 5 Then ActiveCell.Offset(0, -2) = ActiveCell / (1 + [B23]) Exit Sub End If End If Application.ScreenUpdating = True End Sub au passage tant que tu es dans ton Visual Basic tu verras dans la barre Projet un module nommé Module1, clic droit dessus et supprimé, enregistrer etc ... NON reviens sur ta feuille de calcul, en D8 colle cette formule à la place de celle existante =SI(ET(C8<>"";E8<>"");E8-C8;"") et incrémente la vers le bas enregistre ton fichier saisis des valeurs en C et E. Si tu rencontres des problèmes je te retournerais ton fichier

bke_cana · Answer

eh bien d'abord, merci pour ce travail !

j'ai copié comme tu as dis, le code dans le VBA de la feuille 1 ; puis copié la fonction en D8 mais j'ai aucun résultat en entrant une valeur ni en C8 ni en E8 :(

j'ai aussi supprimé le module 1....

je pige rien au code VBA mais la fonction en D8 n'est pas correct: elle permet de calculer D8 (la tva) si on rentre les valeurs C8 ET E8, mais la situation n'est pas celle la.

j'explique réelement le cas de figure:

quand j'achète des pièces détaché souvent chez un fournisseur professionnel, seul le total de la facture est en TTC normal ... mais le prix unitaire de chaque pièce est en HT du coup je peux rentrer que C8 dans mon tableau ( prix HT) et j'aimerais qu'une fonction calcul la part de TVA (D8)  ET le prix TTC (E8) en faisant la somme de C8+D8.

autre cas de figure:
j'achète dans un magasin grand publique est le prix unitaire des articles s'exprime en TTC donc je rentre seulement le TTC de chaque article (E8) et là la fonction calcul la part de la TVA (D8) ET le HT en soustrayant  E8-D8

est-ce possible avec une seul fonction de calculer plusieurs cellules ?

bke_cana · Answer

sinon changer le tableau et faire un peu comme Via55 a proposer:

deux colonne modifiable
PRIX : valeur numerique (HT ou TTC)
TYPE: mention texte ( "HT" ou "TTC")

puis 3 colonnes non modifiable qui ferait un résumé HT TVA TTC en les calculant séparemment en fonction de du TYPE "HT" ou "TTC"

je test les fonction des trois colonnes pour voir ce que ca donne.

Mike-31 · Answer

Re,

Récupère ton fichier et teste les données en colonne C HT et en E TTC et reviens sur la discussion

https://www.cjoint.com/c/CEfqDmn8vAR

bke_cana · Answer

... ce ma fonction de départ, ca n'a pas bougé, 

j'ai vu qu'il y avait du VBA en feuille1 et 2modules mais pas de changement.

Mike-31 · Answer

Re, 

Comment ce ne bouge  pas, si en C8 tu saisis exemple 20 tu as automatiquement en D8 (TVA) 3,92 et en E8 (TTC) 23,92 

si tu fais la saisie inverse en E9 tu saisis le prix TTC 23,92, tu obtiens en D9 3,92 et en C9 prix HT 20 

le code fonctionne bien dans les deux sens, je viens de contrôler plusieurs fois sur Excel 2007 sans le moindre problème, c'est bien ce que j'ai compris à ta demande 
  
A+ 
Mike-31 

Une période d'échec est un moment rêvé pour semer les graines du savoir.

bke_cana · Answer

c'est étrange...

effectivement ce que tu décris correspond exactement a ce que je souhaite mais si j'écris "20" en C8, j'ai 3.92 en D8 mais rien E8... dans l'autre sens pareil, j'ai que la TVA

avec la fonction que j'avais fait au départ:
SI(ESTVIDE(E8);C8*B23;SI(ESTVIDE(C8);E8-E8/(1+B23);""))

tu t'es peu etre trompé de fichier.?

bke_cana · Answer

yes ! le 2eme est bon !! 

le premier avec tes explications sur le coté, c'est le même que j'avais avant, en D j'ai que des #REF! et la fonction des cellules D est: 

=SI(ESTVIDE(E8);C8*#REF!;SI(ESTVIDE(C8);E8-E8/(1+#REF!);"")) 

normal que ca bug vu que #REF! fait référence a la TVA en B23, mais c'est la 1ère formule que j'avais fait. 

L'essentiel c'est que l'autre fonctionne a merveille ! Beau travail ! le pire c'est que la fonction n'est pas compliqué. 
Donc c'est ton algorithme qui fait tout ? 

en tout cas un grand merci !

Mike-31 · Answer

Re, A la place de ta formule =SI(ESTVIDE(E8);C8*$B$23;SI(ESTVIDE(C8);E8-E8/(1+$B$23);"")) utilise plutôt celle ci plus courte et simple vu que la TVA est la différence du prix TTc et du prix HT tous deux calculé à partir du taux de TVA =SI(ET(C8<>"";E8<>"");E8-C8;"") ensuite incrémente la vers le bas. Si tes attentes sont satisfaites je passe le statut de la discussion en résolu

bke_cana · Answer

encore un petit problème annexe:

http://cjoint.com/data3/3EfuE7IR5mJ.htm

j'ai rallonger le tableau C8:C40 et fait glisser les formules mais le calcul ne se fait plus si on rentre le TTC, par contre avec HT ca fonctionne toujours..

j'ai modifié dans le code VBA, la référence de la TVA que j'ai placé en E1 comme ca elle bouge plus. de ce coté tout va bien.

j'ai aussi modifié dans le code VBAla ligne de code:
 If Not Intersect(Selection, Range("C8:C40")) Is Nothing Then
    Target.Select

mais le problème est apparu pour les lignes supérieur a 20.

Mike-31 · Answer

Re
il fallait également modifier le code de la colonne E 


https://www.cjoint.com/c/CEfuO0Z1yCD

bke_cana · Answer

excellent !

par contre j'ai pas compris ce que tu as modifié ?

Mike-31 · Answer

Re,

dans le code il y a cette ligne que tu as modifiée
If Not Intersect(Selection, Range("C8:C40")) Is Nothing Then

mais il y avait également celle ci
If Not Intersect(Selection, Range("E8:E40")) Is Nothing Then

Formule pour calculer prix HT+TVA+TTC

20 réponses

Discussions similaires