Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 02, 2019 09:07 PM
Hi everyone!
I am having a hard time with some formulas due type of cells and blank cells. Hope you can help me.
This is the Nested IF I wrote:
IF( Cuota=PagoEnero, “Pagado”,IF(PagoEnero=BLANK(),“No” , IF(PagoEnero=0,Cuota-PagoEnero ,IF(Cuota-PagoEnero>0, Cuota-PagoEnero))))
The formula returns the same result if the cell is blank or is =0.
I think this is because the statement will always be true in the second one but i dont know how to fix it
My second issue is that, I do the same formula multiple times (Enero, Febrero…) and i need to summarize only the results that returns a number (not text) and make a sum
For example: Enero=0, Febrero=No, Marzo= , … Returns: Enero
CONCATENATE(IF(Enero=BLANK(), “”, IF(Enero=0, “Enero”, IF(Enero>0, “Pagado”))),IF(Febrero>0,"",“Febrero”)…and so on)
I think due the column “Enero” is a formula does not recognize the result as a number, this is not working. I have tried to change text to number and it fixed but brings other problems.
Could anyone give me some advice?
Thank you so much for your answers
Solved! Go to Solution.
Nov 03, 2019 03:36 AM
Welcome to the community, @Ignacio_Salazar! :grinning_face_with_big_eyes: First off, I made a quick translation for myself (and others who don’t speak Spanish) so I could understand more clearly what you’re trying to do:
Cuota = Fee
PagoEnero = PaymentJanuary
Pagado = Paid
Depending on the values of the {Cuota}
and {PagoEnero}
fields, the output from that first formula will either be a status indicator—“Pagado” or “No”—or a number representing some payment value. Formula logic aside, I believe that mixing output types like that is part of the problem you’re encountering.
From my experience, clean data modeling is important for a variety of reasons, and one key way to keep a data model clean is to be as specific as possible about what each field represents. Having a field that represents either a status or a numerical value leads to greater complexity and confusion, not greater clarity.
I strongly suggest splitting the logic of that first formula across two fields: one to contain only the status, the other to contain only the appropriate payment value. Even though it will mean adding more fields, you will likely find it much easier to write your formulas because you’re not mixing different concepts and data types in the same place.
Nov 03, 2019 03:36 AM
Welcome to the community, @Ignacio_Salazar! :grinning_face_with_big_eyes: First off, I made a quick translation for myself (and others who don’t speak Spanish) so I could understand more clearly what you’re trying to do:
Cuota = Fee
PagoEnero = PaymentJanuary
Pagado = Paid
Depending on the values of the {Cuota}
and {PagoEnero}
fields, the output from that first formula will either be a status indicator—“Pagado” or “No”—or a number representing some payment value. Formula logic aside, I believe that mixing output types like that is part of the problem you’re encountering.
From my experience, clean data modeling is important for a variety of reasons, and one key way to keep a data model clean is to be as specific as possible about what each field represents. Having a field that represents either a status or a numerical value leads to greater complexity and confusion, not greater clarity.
I strongly suggest splitting the logic of that first formula across two fields: one to contain only the status, the other to contain only the appropriate payment value. Even though it will mean adding more fields, you will likely find it much easier to write your formulas because you’re not mixing different concepts and data types in the same place.
Nov 03, 2019 08:53 AM
Thank you so much for your answer.
I did not take a number representing some payment value because I use this number to summarize payment but also to show if it was made that payment… :rofl:
I think splitting in two fields is the better way. I have tried this because I call this values to an app and is a bit difficult to make that reference from 2 fields… and also because I have not removed from my head the logic of the spreedsheets
I also understood when i saw post and videos of difference between data base and spreedsheets
PS. thanks for your translation. I made the translation in an image but I dont know why I cant show it :grinning_face_with_big_eyes:
Nov 03, 2019 09:41 AM
Just a quick question. What abaout blank cells and cells= 0? Is there an option to difference it besides COUNTA?
Nov 03, 2019 12:37 PM
Can you be more specific? I’m not sure what you’re asking.