# Formulas: Nested if and summarize

Topic Labels: Formulas
Solved
1859 4
cancel
Showing results for
Did you mean:
4 - Data Explorer

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?

1 Solution

Accepted Solutions
18 - Pluto

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

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.

4 Replies 4
18 - Pluto

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

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.

4 - Data Explorer